SQL Server 無日誌檔 (LDF) 情況下的資料庫緊急恢復實戰

誤刪 .ldf 交易記錄檔、記錄檔損壞或磁碟故障導致資料庫進入 SUSPECT（置疑）狀態，是 DBA 面臨的最高壓情境之一。本文整理兩套經實戰驗證的恢復方案：方案一「緊急模式 + 系統表修改」適用於輕度不一致；方案二「重建資料庫 + 單一檔案附加」適用於記錄檔完全遺失且資料檔完整。全程附帶 T-SQL 腳本與關鍵決策點說明。

核心原理速覽

• SQL Server 啟動時需執行 復原：根據 LDF 將未完成交易回滾、已提交交易重做。
• 缺少 LDF → 無法完成復原 → 資料庫標記為 SUSPECT。
• EMERGENCY 模式：僅讀、單一使用者、繞過復原流程，允許 DBCC 修復與資料導出。
• sp_attach_single_file_db：僅以 MDF 建立資料庫，SQL Server 自動建立新的空白 LDF（原記錄內容永久遺失）。

方案一：緊急模式 + 系統表修改（適用於 MDF 完整、LDF 遺失/損壞、資料一致性尚可接受）

1. 建立同名空資料庫（佔位用）：
   CREATE DATABASE [RecoverDB] ON PRIMARY (NAME='RecoverDB', FILENAME='D:\MSSQL\Data\RecoverDB.mdf') LOG ON (NAME='RecoverDB_Log', FILENAME='D:\MSSQL\Log\RecoverDB_Log.ldf');
2. 停止 SQL Server 服務（net stop MSSQLSERVER），不要 執行 sp_detach_db。
3. 用原始 MDF 覆蓋剛建立的空 MDF（保留原始資料頁）。
4. 重新啟動 SQL Server，資料庫將呈現 SUSPECT。
5. 啟用系統表更新並進入緊急模式：

USE master;
GO
EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
GO
UPDATE sysdatabases SET status = 32768 WHERE name = 'RecoverDB'; -- 32768 = EMERGENCY
GO
EXEC sp_dboption 'RecoverDB', 'single user', 'true';
GO

6. 執行一致性檢查與修復（視資料重要性選擇 REPAIR_ALLOW_DATA_LOSS 為最後手段）：

DBCC CHECKDB ('RecoverDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; -- 僅檢查
-- 若需修復（謹慎！可能遺失資料）：
-- DBCC CHECKDB ('RecoverDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

7. 恢復正常狀態並關閉系統表更新：

UPDATE sysdatabases SET status = 28 WHERE name = 'RecoverDB'; -- 28 = 正常
GO
EXEC sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_dboption 'RecoverDB', 'single user', 'false';
GO

8. 驗證與導出：
    • SELECT * FROM RecoverDB..KeyTable 確認資料可讀。
    • 建議立即以 「產生指令碼」+「BCP/SSIS 匯出」** 方式將資料遷移至全新資料庫，避免殘留隱性不一致。

USE master;
GO
EXEC sp_dboption 'RecoverDB', 'single user', 'true';
GO
EXEC sp_detach_db 'RecoverDB';                     -- 分離（會保留 MDF）
GO
EXEC sp_attach_single_file_db @dbname = 'RecoverDB',
    @physname = 'D:\MSSQL\Data\RecoverDB.mdf';      -- 僅附加 MDF，自動建新 LDF
GO
EXEC sp_dboption 'RecoverDB', 'single user', 'false';
GO