SQL Server 資料庫備份與還原完全手冊：脫機檔案複製與僅有 MDF 檔案的災難恢復

兩大備份路徑

1. BACKUP DATABASE：官方標準備份指令，支援完整/差異/記錄備份、壓縮、加密、鏡像。
2. 脫機檔案複製：停止服務或卸離資料庫，直接複製 .mdf（資料檔）與 .ldf（記錄檔）。本文深入探討此法及其還原變體。

一、正常脫機備份與附加還原

核心指令

• 卸離：sp_detach_db 'db_name'
• 附加（雙檔）：sp_attach_db 'db_name', 'mdf_path', 'ldf_path'
• 附加（單檔，自動重建記錄檔）：sp_attach_single_file_db 'db_name', 'mdf_path'

範例

-- 卸離
sp_detach_db 'test';

-- 附加（指定雙檔）
sp_attach_db 'test',
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf';

-- 僅附加 MDF（自動建立新 LDF）
sp_attach_single_file_db 'test', 'C:\...\test_data.mdf';

關鍵：備份時必須同時取得 .mdf 與 .ldf，否則屬於災難恢復場景。

二、僅有 MDF 檔案的災難恢復（緊急模式 + 重建記錄檔）

適用場景：僅備份了 MDF、或從他機複製 MDF 無 LDF、正常附加報錯（裝置啟動錯誤、無法開啟資料庫）。

步驟詳解

1. 建立同名空資料庫（Enterprise Manager 或 CREATE DATABASE），作為容器。
2. 停止 SQL Server 服務。
3. 替換檔案：刪除新資料庫的 ldf，用目標 mdf 覆蓋新資料庫的 mdf。
4. 啟動服務：資料庫狀態變為「置疑」。
5. 允許系統表直接修改：

   USE master; GO
   sp_configure 'allow updates', 1; GO
   RECONFIGURE WITH OVERRIDE; GO
   

6. 設為緊急模式：

   UPDATE sysdatabases SET status = -32768 WHERE dbid = DB_ID('test');
   

   資料庫進入「唯讀/置疑/離線/緊急」模式，僅可見系統表。
7. 重建記錄檔：

   DBCC REBUILD_LOG('test', 'C:\...\test_log.ldf');
   

   若報「無法獨佔鎖定」，關閉所有連線（含 EM 開啟的系統表）重試。成功訊息含「日誌已重建、交易一致性遺失、請執行 DBCC CHECKDB」。
8. 驗證一致性：DBCC CHECKDB('test')，預期 0 配置錯誤、0 一致性錯誤。
9. 恢復正常模式：sp_dboption 'test', 'dbo use only', 'false'。
10. 關閉系統表直接修改：

    sp_configure 'allow updates', 0; GO
    RECONFIGURE WITH OVERRIDE; GO
    

關鍵提醒

• 步驟 5-6 需 sysadmin 權限，操作風險高，務必在測試環境演練。
• REBUILD_LOG 會丟失未提交交易，資料可能不一致，驗證極重要。
• SQL Server 2005+ 建議使用 CREATE DATABASE ... FOR ATTACH_REBUILD_LOG 替代手動緊急模式流程。
• 平時養成完整備份 + 記錄備份習慣，避免陷入此類災難恢復。