SQL Server 修改資料庫檔案與記錄檔儲存路徑完整指南

三種修改層級

1. 查詢目前檔案路徑

SELECT 
    database_id, 
    name, 
    physical_name AS CurrentLocation,
    state_desc,
    size * 8 / 1024.0 AS Size_MB
FROM sys.master_files 
WHERE database_id = DB_ID(N'資料庫名');

2. 修改單一資料庫檔案路徑（需重啟）

-- 範例：將 testDb 移至 G:\SQL_DATA\testDb\
ALTER DATABASE testDb MODIFY FILE (NAME = testDb,     FILENAME = 'G:\SQL_DATA\testDb\testDb.mdf');
ALTER DATABASE testDb MODIFY FILE (NAME = testDb_log, FILENAME = 'G:\SQL_DATA\testDb\testDb_log.ldf');

1. 執行上述 ALTER DATABASE ... MODIFY FILE。
2. 離線資料庫：ALTER DATABASE testDb SET OFFLINE WITH ROLLBACK IMMEDIATE;
3. 用檔案總管將 .mdf/.ldf 實體移動至新路徑。
4. 上線資料庫：ALTER DATABASE testDb SET ONLINE;

注意：MODIFY FILE 僅更新系統目錄中的路徑中繼資料，**不會移動實體檔案**，必須手動移動檔案並重啟資料庫（或離線/上線）生效。

3. 修改預設資料/記錄檔路徑（立即生效，影響後續新建資料庫）

-- 修改預設資料檔路徑
EXEC xp_instance_regwrite 
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
    @value_name = N'DefaultData',
    @type = N'REG_SZ',
    @value = N'E:\MSSQL_MDF\Data';

-- 修改預設記錄檔路徑
EXEC master..xp_instance_regwrite 
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
    @value_name = N'DefaultLog',
    @type = N'REG_SZ',
    @value = N'E:\MSSQL_MDF\Log';

執行後，**新建資料庫**預設會將檔案放在新路徑，**現有資料庫不受影響**。

4. 透過 SSMS 圖形化修改（同樣需重啟）

1. SSMS 物件總管 → 右鍵資料庫 → 屬性 → 檔案。
2. 修改「路徑」欄位為新資料夾。
3. 確定 → 離線/移動檔案/上線。

常見坑點與檢核表

• [ ] **SQL Server 服務帳戶**對新資料夾具備「完全控制」NTFS 權限。
• [ ] 新路徑磁碟空間充足（建議預留 2x 預估成長）。
• [ ] 叢集環境：新路徑必須在**共用磁碟**或 **Cluster Shared Volume (CSV)** 上。
• [ ] Always On 可用性群組：主/次複本皆需相同路徑結構，或啟用 FILE_STREAM 目錄一致性。
• [ ] 修改 tempdb 路徑需重啟 SQL Server 服務（而非單一資料庫）。
• [ ] 記錄檔移動後，若資料庫處於「復原中」，檢查 log_reuse_wait_desc。

驗證腳本

SELECT name, physical_name 
FROM sys.master_files 
WHERE database_id = DB_ID(N'資料庫名');