SQL Server 遺失交易記錄檔（LDF）資料庫災難恢復：兩大實戰方案完整紀錄

誤刪 SQL Server 交易記錄檔（.ldf）是資料庫管理員最噩夢的場景之一。微軟官方文件明確警告：「務必確保記錄檔存在，它至關重要」，甚至有 KB 文章專述「僅靠記錄檔恢復資料庫」。本文記錄兩種無記錄檔情況下的實戰恢復方案，含完整 T-SQL 腳本、操作步驟與避坑指南。

方案一：建立同名資料庫覆蓋法（標準緊急恢復流程）

適用場景

• 資料檔（.mdf）完好無損
• 記錄檔遺失或損壞
• 資料庫處於「置疑」狀態

操作步驟

1. 建立同名空資料庫：在 Enterprise Manager 或 T-SQL 中建立同名稱、同檔案路徑的新資料庫
2. 停止 SQL Server 服務：切勿執行「卸離資料庫」，直接停止服務（SQL Server Configuration Manager 或 net stop MSSQLSERVER）
3. 覆蓋資料檔：用原資料庫的 .mdf（含 .ndf）覆蓋剛建立的新資料庫檔案
4. 重啟 SQL Server 服務
5. 進入緊急修復模式：資料庫將標記為「置疑」，執行下列腳本重建記錄檔並修復

核心修復腳本（請替換 置疑的資料庫名）

USE MASTER
GO
-- 允許直接更新系統表（高風險操作，僅限緊急時刻）
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
GO

-- 將資料庫狀態設為 32768（緊急模式 / Emergency Mode）
UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = N'置疑的資料庫名'
GO

-- 設為單用戶模式，防止其他連線干擾
EXEC sp_dboption N'置疑的資料庫名', 'single user', 'true'
GO

-- 執行一致性檢查並嘗試修復（REPAIR_ALLOW_DATA_LOSS 為最後手段）
DBCC CHECKDB(N'置疑的資料庫名', REPAIR_ALLOW_DATA_LOSS)
GO

-- 恢復正常狀態值 28（線上、多用戶）
UPDATE SYSDATABASES SET STATUS = 28 WHERE NAME = N'置疑的資料庫名'
GO

-- 關閉系統表直接更新
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
GO

-- 恢復多用戶模式
EXEC sp_dboption N'置疑的資料庫名', 'single user', 'false'
GO

後續建議

修復完成後，資料庫雖可存取，但結構可能受損。最佳做法：使用 Generate Scripts（產生腳本） 重建資料庫結構，再透過 BCP、SSIS 或 INSERT ... SELECT 導入資料，建立乾淨的新資料庫替代。

方案二：真實案例複盤——從誤刪 3GB LDF 到緊急模式求生

事故背景

• 內部應用資料庫磁碟空間不足
• XXX_Data.ldf 膨脹至 3GB
• 嘗試「收縮資料庫」無效後，誤刪除 LDF 檔案（職業生涯最大失誤）
• 資料庫無連線、Enterprise Manager 標記「(置疑)」、從無完整備份
• 僅有半年前遷移的舊伺服器備份，缺大量記錄、資料表與預存程序

失效嘗試記錄（避坑指南）

緊急模式操作流程（參考）

1. 將資料庫設為 EMERGENCY 模式：ALTER DATABASE XXX SET EMERGENCY
2. 嘗試重建 Log 檔（DBCC REBUILD_LOG 或手動建立）
3. 重啟 SQL Server 服務
4. 設為單用戶模式：ALTER DATABASE XXX SET SINGLE_USER
5. 執行 DBCC CHECKDB 修復
6. 無嚴重錯誤則恢復狀態，記得關閉 ALLOW UPDATES

關鍵發現與殘留問題

經實測：移走 MDF → 建立同名資料庫 → 停服務 → 覆蓋 MDF → 按緊急模式流程，除第 2 步重建 Log 失敗外，其餘步驟均成功。資料庫雖仍標記「置疑」，但可執行 SELECT 查詢，成功導出核心業務資料。

但應用程式連線時仍報錯：

錯誤：-2147467259
未能在資料庫 XXX 中執行 BEGIN TRANSACTION，
因為該資料庫處於繞過恢復模式。

最終解決策略

1. 利用 SELECT 可用的窗口期，全量導出所有資料表資料（BCP/SSIS/連結伺服器）
2. 以半年前備份為藍本，重建乾淨資料庫結構
3. 導入導出的資料，補齊缺失的預存程序、觸發器、索引
4. 驗證應用程式功能正常後，替換舊資料庫

血淚教訓與預防清單

• 絕不手動刪除 LDF 檔案，收縮失效時應檢查開放交易、備份模式、虛擬記錄檔碎片
• 落實備份策略：完整備份 + 定期記錄備份 + 定期還原演練
• 監控磁碟空間，設定自動成長上限與警示
• 緊急時刻先導出資料再嘗試修復，資料優於結構
• 熟記 EMERGENCY、SINGLE_USER、DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS) 組合拳，但視為最後手段