SQL Server 資料庫檔案附加還原：sp_attach_db 實戰操作指南

適用場景

擁有完整的資料庫資料檔（.mdf）與記錄檔（.ldf），需在同版本或更高版本的 SQL Server 實例上快速掛載為線上資料庫。常見於硬體遷移、測試環境複製、災難恢復演練。

核心指令：sp_attach_db

EXEC sp_attach_db
    @dbname = N'MyDatabase',
    @filename1 = N'D:\MSSQL\Data\MyDatabase_Data.mdf',
    @filename2 = N'D:\MSSQL\Data\MyDatabase_Log.ldf';
-- 若有多個次要資料檔（.ndf），依序加入 @filename3, @filename4 ...

完整操作流程

1. 來源端： 確保資料庫處於乾淨關閉狀態（正常停止 SQL Server 服務或執行 sp_detach_db 分離）。複製 所有 .mdf、.ndf、.ldf 檔案至目標伺服器對應路徑。
2. 目標端： 確認 SQL Server 服務執行帳號對目標資料夾具備讀寫權限（NTFS ACL）。
3. 執行附加： 在 SSMS / sqlcmd / osql 執行上述 sp_attach_db 指令。
4. 驗證： SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase'; 應顯示 ONLINE。執行 DBCC CHECKDB('MyDatabase') 驗證一致性。

僅附加資料檔（重建記錄檔）：sp_attach_single_file_db

EXEC sp_detach_db @dbname = N'MyDatabase';  -- 先確保已分離
EXEC sp_attach_single_file_db
    @dbname = N'MyDatabase',
    @physname = N'D:\MSSQL\Data\MyDatabase_Data.mdf';

效果： SQL Server 自動建立新的記錄檔（預設 1 MB，自動成長）。警告： 原有記錄檔內未提交/未檢查點的交易將遺失，資料庫可能處於交易不一致狀態，僅限非生產、可接受資料遺失環境使用。

常見錯誤與排除

版本演進說明

• SQL Server 2005+：sp_attach_db 仍支援但標記為淘汰，建議改用 CREATE DATABASE ... FOR ATTACH / FOR ATTACH_REBUILD_LOG。
• SQL Server 2012+：支援 FILESTREAM、FileTable、In-Memory OLTP 檔案群組附加，語法相同但需對應檔案群組路徑。

最佳實務

1. 附加前先在測試環境驗證 DBCC CHECKDB 無誤。
2. 附加後立即更新統計資料：EXEC sp_updatestats;
3. 檢查孤立使用者：EXEC sp_change_users_login 'Report'; → 'Auto_Fix' 修復。
4. 調整資料庫選項：復原模式、自動關閉、自動縮減、頁面驗證（CHECKSUM）等。