MS SQL 基礎教學：資料庫移動完整實戰 (sp_detach_db/sp_attach_db 分離附加法、檔案系統層級移動、驗證步驟)

資料庫移動最常用分離/附加法。本文完整實戰示範：1) 查詢檔案資訊：EXEC sp_helpdb 'mytest' 取得邏輯名/物理路徑/大小；2) 分離：EXEC sp_detach_db 'mytest', true (@skipchecks=true 跳過 UPDATE STATISTICS)；3) 檔案系統移動：剪貼 .mdf/.ldf 至新目標目錄 (如 E:\SQL Data)；4) 附加：EXEC sp_attach_db @dbname='mytest', @filename1='E:\SQL Data\mytest_data.mdf', @filename2='E:\SQL Data\mytest_log.ldf'；5) 驗證：EM 檢視屬性 / sp_helpdb 確認新路徑。重要限制：sp_attach_db 僅支援最多 16 個檔案，超過需用 CREATE DATABASE ... FOR ATTACH；僅能附加曾由 sp_detach_db 分離的資料庫。附各步驟執行輸出截圖。

分離/附加核心 T-SQL

-- 1. 查詢檔案
EXEC sp_helpdb mytest;

-- 2. 分離 (skipchecks=true 跳過 UPDATE STATISTICS 加速)
EXEC sp_detach_db 'mytest', true;
-- 輸出: Successfully detached database 'mytest'.

-- 3. OS 層級移動 .mdf/.ldf 至新目錄

-- 4. 附加 (最多 16 檔案，需含主資料檔)
EXEC sp_attach_db @dbname = 'mytest',
  @filename1 = 'e:\sql data\mytest_data.mdf',
  @filename2 = 'e:\sql data\mytest_log.ldf';
-- 輸出: Successfully attached database 'mytest'.

關鍵注意事項

• 分離期間資料庫離線，不可存取。
• 資料庫擁有者 (dbo) 不變，但若移至不同伺服器需修復孤立使用者 (sp_change_users_login)。
• 檔案權限：SQL Server 服務帳號需對新目錄有完整權限。
• 超過 16 檔案：CREATE DATABASE mytest ON (FILENAME='...'), (FILENAME='...') FOR ATTACH;
• 替代方案：備份/還原 (支援異地/跨版本/REPLACE/NORECOVERY/STATS/MOVE 選項)。

適用場景選擇指南