SQL Server 資料庫檔案級備份還原技術：MDF/LDF 脫離附加與僅 MDF 災難復原

兩大備份策略對比

標準檔案級脫離/附加流程

-- 1. 脫離資料庫（斷開所有連線）
EXEC sp_detach_db 'test';

-- 2. OS 層面複製 test_data.mdf、test_log.ldf 至目標機器

-- 3. 附加資料庫（雙檔）
EXEC sp_attach_db 
    @dbname = 'test',
    @filename1 = 'D:\MSSQL\Data\test_data.mdf',
    @filename2 = 'D:\MSSQL\Data\test_log.ldf';

-- 4. 僅附加 MDF（自動重建 LDF）
EXEC sp_attach_single_file_db 
    @dbname = 'test',
    @physname = 'D:\MSSQL\Data\test_data.mdf';

關鍵點：必須同時備份 .mdf（資料檔）與 .ldf（日誌檔），否則交易一致性無法保證。

僅擁有 MDF 檔案的災難復原

情境 A：同一伺服器、同一資料庫產生的 MDF

直接 sp_attach_single_file_db 通常成功，會出現警告：

裝置啟動錯誤。實體檔名 '...test_Log.LDF' 可能有誤。已建立新日誌檔 '...test_log.LDF'。

資料庫上線，但未認可交易遺失。

情境 B：跨伺服器/不同資料庫來源的 MDF（常見失敗）

錯誤：Msg 1813, Level 16, State 2 無法開啟新資料庫，裝置啟動錯誤。

逐步修復程序（緊急模式重建日誌）

1. 建立同名空資料庫（企業管理器或 CREATE DATABASE）
2. 停止 SQL Server 服務
3. 覆蓋檔案：刪除新建的 _log.ldf，用來源 .mdf 覆蓋新建的 _data.mdf
4. 啟動服務：資料庫進入「疑慮」狀態
5. 啟用系統表直接修改：

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

6. 設定緊急模式：

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

7. 重建日誌：

   DBCC REBUILD_LOG('test', 'D:\MSSQL\Data\test_log.ldf');
   

   若報 Msg 5030 無法獨佔鎖定，關閉企業管理器/其他連線後重試。

8. 驗證一致性：

   DBCC CHECKDB('test');
   

9. 還原正常模式：

   
   EXEC sp_dboption 'test', 'dbo use only', 'false';
   EXEC sp_configure 'allow updates', 0;
   RECONFIGURE WITH OVERRIDE;
   

風險與限制

• 交易遺失：重建日誌 = 放棄未認可交易，資料可能不一致
• 系統表修改：allow updates 極度危險，僅限最後手段
• 版本相容：MDF 格式隨版本變更，SQL 2000 MDF 無法附加到 SQL 2005+ 直接使用
• 加密/壓縮：TDE 加密資料庫需憑證/金鑰，單純複製檔案無法還原

現代最佳實務：取代檔案級操作

1. 完整備份 + 壓縮 + 加密：BACKUP DATABASE ... TO DISK = ... WITH COMPRESSION, ENCRYPTION
2. 可用性群組 / 鏡像 / 複寫：高可用架構取代手工檔案複製
3. 容器/雲端快照：Azure 快照、AWS EBS 快照、VMware VADP
4. Dev/Test 環境：DBCC CLONEDATABASE（僅結構統計）、BACKUP/RESTORE 自動化腳本