SQL Server 資料庫檔案級備份還原與損壞修復完整手冊

針對 .mdf (資料檔) 與 .ldf (日誌檔) 的檔案級備份還原，涵蓋正常附加、僅有 MDF 強制重建日誌、系統表更新 (舊版) 等完整技術路徑。

正常附加 (MDF+LDF 完整)

企業管理器 GUI

1. 右鍵「資料庫」→「所有工作」→「附加資料庫」。
2. 選擇 .mdf 檔案，自動關聯 .ldf，確定。

T-SQL 指令

EXEC sp_attach_db 'MyDB', 'D:\Data\MyDB.mdf', 'D:\Data\MyDB_log.ldf';
-- 或
EXEC sp_attach_single_file_db 'MyDB', 'D:\Data\MyDB.mdf';

僅有 MDF 檔案的強制恢復 (核心難點)

情境 A：MDF 來自同一伺服器，近期有 Checkpoint

EXEC sp_attach_single_file_db 'MyDB', 'D:\Data\MyDB.mdf';
-- 可能出現警告：已建立新日誌檔，事務一致性遺失，需 DBCC CHECKDB

情境 B：MDF 來自不同伺服器/無 Checkpoint → 附加失敗

Msg 1813, Level 16, State 2: Could not open new database 'MyDB'. CREATE DATABASE aborted.
Device activation error. Physical file name '...ldf' may be incorrect.

完整修復流程 (適用 SQL Server 7.0/2000/2005/2008/2012/2014/2016/2017/2019)

1. 建立同名空資料庫 (SSMS 右鍵新增或 CREATE DATABASE MyDB)。
2. 離線/停止服務：ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE 或停止 MSSQLSERVER 服務。
3. 替換檔案：刪除新建資料庫的 MyDB_log.ldf，用備份的 MyDB.mdf 覆蓋 MyDB.mdf。
4. 啟動服務/上線：資料庫進入 SUSPECT (置疑) 狀態。
5. 啟用系統表更新 (舊版) / 現代改用 EMERGENCY 模式：

   -- 現代通用做法 (2005+)
   ALTER DATABASE MyDB SET EMERGENCY;
   GO
   ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   GO
   DBCC CHECKDB ('MyDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
   GO
   ALTER DATABASE MyDB SET MULTI_USER;
   GO

6. 驗證：DBCC CHECKDB('MyDB') → 0 配置/一致性錯誤。
7. 恢復選項：EXEC sp_dboption 'MyDB', 'dbo use only', 'false' (舊版) / 已在 MULTI_USER 完成。
8. 關閉系統表更新 (若啟用)：sp_configure 'allow updates',0; RECONFIGURE WITH OVERRIDE。

關鍵參數對照

舊版 (7.0/2000)	現代版 (2005+)	說明
sp_configure 'allow updates',1	ALTER DATABASE SET EMERGENCY	允許修改系統目錄 / 進入緊急唯讀模式
UPDATE sysdatabases SET status=-32768	(同上)	標記為緊急模式
DBCC REBUILD_LOG	DBCC CHECKDB ... REPAIR_ALLOW_DATA_LOSS	重建交易日誌
sp_dboption 'dbo use only', 'false'	ALTER DATABASE SET MULTI_USER	恢復多用戶存取

避坑提醒

• 備份時務必同時複製 MDF 與 LDF，單獨備份 MDF 風險極高。
• REPAIR_ALLOW_DATA_LOSS 會刪除損壞頁面，導致資料遺失，執行前必須備份原始 MDF。
• 修復後必須重建索引/統計資訊，否則效能極差。
• 生產環境嚴禁直接更新系統表 (allow updates)，務必用 EMERGENCY 模式。