SQL Server 資料庫搬家指南：資料檔與記錄檔移至新磁碟

核心概念

資料庫由兩類檔案組成：資料檔（.mdf 主檔、.ndf 次檔）與交易記錄檔（.ldf）。搬家本質是將這兩類檔案移至新路徑，並更新系統目錄中的路徑資訊。

一、使用者資料庫搬家（兩種方法）

方法 1：SSMS 圖形介面（分離/附加）

1. SSMS 物件總管 → 右鍵目標資料庫 → 工作 → 分離 → 勾選「卸除連線」 → 確定。
2. 用檔案總管將 .mdf、.ldf 從舊路徑（如 D:\MSSQL\Data）複製/移動到新路徑（如 E:\SQLDataNew）。
3. SSMS → 右鍵「資料庫」資料夾 → 工作 → 附加 → 「加入」選擇新路徑的 .mdf → 確定。

方法 2：T-SQL 系統預存程序（適合自動化腳本）

-- 1. 分離
USE master;
GO
EXEC sp_detach_db 'MyDB';
GO

-- 2. 移動檔案（OS 層面 copy/move）

-- 3. 附加
USE master;
GO
EXEC sp_attach_db
    @dbname = 'MyDB',
    @filename1 = 'E:\SQLDataNew\MyDB_data.mdf',
    @filename2 = 'E:\SQLDataNew\MyDB_log.ldf';
GO

二、系統資料庫搬家（需特殊處理）

1. master 資料庫

1. SSMS → 右鍵伺服器實例 → 屬性 → 啟動參數。
2. 修改 -d（資料檔路徑）與 -l（記錄檔路徑）為新路徑，例如：

   -dE:\SQLDataNew\master.mdf
   -lE:\SQLDataNew\mastlog.ldf
   

3. 停止 SQL Server 服務（net stop MSSQLSERVER）。
4. 將 master.mdf、mastlog.ldf 移至新路徑。
5. 啟動服務（net start MSSQLSERVER）。

救急技巧：若啟動參數填錯導致無法啟動，可修改登錄機碼： HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\Parameters 下的 SQLArg1（-d）、SQLArg2（-l）。

2. msdb / model 資料庫

1. 確保 SQL Server Agent 服務已停止（msdb 正在使用時無法分離）。
2. 啟動參數新增 -T3608（僅復原 master，跳過其他資料庫啟動）。
3. 重啟 SQL Server 服務。
4. 分離：EXEC sp_detach_db 'msdb';（model 同理）。
5. 移動檔案至新路徑。
6. 附加：EXEC sp_attach_db 'msdb', 'E:\New\msdbdata.mdf', 'E:\New\msdblog.ldf';
7. 移除啟動參數 -T3608，重啟服務。

3. tempdb 資料庫（最簡單）

USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLDataNew\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLDataNew\templog.ldf');
GO
-- 重啟 SQL Server 服務生效

tempdb 每次啟動重建，無需分離/附加，MODIFY FILE 後重啟即可。

三、替代方案：ALTER DATABASE MODIFY FILE（適用於使用者資料庫，SQL Server 2005+）

-- 1. 離線資料庫
ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

-- 2. 修改路徑
ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB_data, FILENAME = 'E:\SQLDataNew\MyDB_data.mdf');
ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB_log,  FILENAME = 'E:\SQLDataNew\MyDB_log.ldf');
GO

-- 3. 移動實體檔案（OS 層面）

-- 4. 上線資料庫
ALTER DATABASE MyDB SET ONLINE;
GO

優點：不需分離/附加，保留資料庫屬性、權限、Trustedworthy 設定等。缺點：需短暫離線。

四、檢查清單

小結

• 使用者資料庫：推薦 ALTER DATABASE MODIFY FILE + 離線/移動/上線，保留中繼資料。
• master：修改啟動參數 + 服務重啟。
• msdb/model：需 -T3608 追蹤旗標協助。
• tempdb：直接 MODIFY FILE + 重啟。
• 無論哪種方式，服務帳號權限與備份是兩大前提。