SQL Server 資料庫檔案移動最佳實踐：sp_detach_db / sp_attach_db 離線遷移指南

為何需要離線移動？

資料庫檔案 (.mdf/.ldf) 在服務運行時被獨佔鎖定，無法直接複製/移動。必須先分離 釋放鎖定，移動檔案後再附加 回服務。

分離資料庫：sp_detach_db

EXEC sp_detach_db 'MyDatabase';

• 僅限 sysadmin 固定伺服器角色執行。
• 自動對所有資料表執行 UPDATE STATISTICS，確保統計資訊最新再離線。
• 分離後資料庫從 `sys.databases` 移除，檔案釋放可自由移動/複製。

附加資料庫：sp_attach_db

EXEC sp_attach_db 'MyDatabase',
    @filename1 = 'D:\NewLocation\MyDatabase_Data.mdf',
    @filename2 = 'D:\NewLocation\MyDatabase_Log.ldf';

• 至少指定主資料檔 (.mdf)，記錄檔 (.ldf) 可選 (若省略會嘗試原路徑或自動建立)。
• 檔案路徑必須為 SQL Server 服務帳號可存取的本地路徑 (UNC 路徑需權限)。
• 附加成功後資料庫重新上線，可正常存取。

完整遷移流程

1. 確認無作用中連線：EXEC sp_who2 檢查該資料庫 SPID，必要時 KILL 或設定 SINGLE_USER 滾斷連線。
2. 執行 sp_detach_db。
3. 在檔案系統層級移動/複製 .mdf/.ldf 至新磁碟/目錄 (建議使用 Robocopy 保留權限/屬性)。
4. 執行 sp_attach_db 指定新路徑。
5. 驗證：DBCC CHECKDB、查詢核心業務表、檢查 `sys.master_files` 路徑正確。
6. 更新連線字串/別名 (如使用檔案路徑連線的舊應用)。

適用場景與替代方案

注意事項

• 分離會移除資料庫鎖定與中繼資料，不可在複寫發布者/訂閱者、鏡像、記錄傳送角色資料庫上直接分離，需先移除拓撲角色。
• 附加時若資料庫曾啟用 TRUSTWORTHY、DB_CHAINING、BROKER 等選項，需重新設定。
• 大型資料庫移動耗時，規劃足夠停機窗口並預留回滾方案 (保留原檔案直到驗證通過)。

1. 上一篇文章： SQL Server 使用新名稱還原資料庫：RESTORE FILELISTONLY 與 MOVE 選項實戰
2. 下一篇文章： sp_stored_procedures + sp_helptext：秒級找回遺忘存儲過程並閱讀源碼

關於SqlServer

SQL Server 實務筆記：IIS 安裝、實例重命名與連線技巧

記錄幾個實務上常遇到的 SQL Server 設定與連線細節

SQL Server 索引缺失診斷：DMV 深度實戰與 DTA 輔助優化

為何需要 DMV？ 面對複雜查詢，人工判斷最佳索引組合極難。

SQL Server 2008 卸載疑難雜症：找不到卸載項、重啟失敗完美解決

問題一：控制面板找不到 SQL Server 卸載項 原因：

SQL Server 遺失 sa 密碼與 Windows 登入被刪：單一使用者模式緊急修復指南

最棘手的 SQL Server 存取失效情境：sa 停用、僅