您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 記錄檔 (LDF) 遺失災難修復完整指南：無作用中交易 vs 有作用中交易兩種情境應對

編輯：關於SqlServer

交易記錄檔 (LDF) 是資料庫復原的靈魂，遺失 LDF 將導致資料庫進入 SUSPECT/RECOVERY_PENDING 狀態。修復策略取決於遺失時記錄檔是否包含**作用中交易**。本文詳細說明兩種情境的分級修復步驟，從簡單的分離附加到複雜的緊急模式搶救與資料匯出重建。

一、情境判斷：如何確認是否有作用中交易？

嘗試 sp_attach_single_file_db 僅附加 MDF → 若報錯 「記錄檔不符，無法附加」，通常代表有作用中交易 (或檢查點未截斷)。
檢查 DBCC CHECKDB 輸出、錯誤日誌、sys.databases.log_reuse_wait_desc。

二、情境 A：無作用中交易 (記錄檔已截斷/無開啟交易) — 簡單修復

分離資料庫：
```
EXEC sp_detach_db 'DBName';
```
或 SSMS 右鍵資料庫 → 工作 → 分離。
僅附加 MDF 自動重建 LDF：
```
EXEC sp_attach_single_file_db 'DBName', 'D:\Data\DBName.mdf';
```
或 SSMS 右鍵資料庫資料夾 → 附加 → 選擇 .mdf → 移除 .ldf 列表項 → 確定。

SQL Server 會自動建立新的空白記錄檔 (1MB 起)，資料庫上線。**資料零損失 (前提：確實無作用中交易)。**

三、情境 B：有作用中交易 — 進階搶救流程 (高風險，需極度謹慎)

核心邏輯：建立同名空資料庫 → 置換 MDF → 進入 EMERGENCY 模式 (繞過復原) → 單用戶模式修復 → 匯出資料重建新庫。

步驟 1：建立同名空資料庫並置換 MDF

停止 SQL Server 服務 (`net stop mssqlserver`)。
將原 MDF 暫存備份 (如 `DBName.mdf.bak`)。
啟動 SQL Server，建立同名空資料庫 `CREATE DATABASE DBName;` (會產生新 MDF/LDF)。
停止 SQL Server，用備份的原 MDF **覆蓋** 新建的 MDF。
啟動 SQL Server，資料庫將進入 **RECOVERY_PENDING / SUSPECT** 狀態。

步驟 2：啟用系統表更新並進入 EMERGENCY 模式


USE master;
GO
EXEC sp_configure 'allow updates', 1; -- 允許直接更新系統表 (SQL 2000/2005 僅支援)
RECONFIGURE WITH OVERRIDE;
GO

UPDATE sysdatabases SET status = 32768 WHERE name = 'DBName'; -- 32768 = EMERGENCY 模式
GO

注意：SQL Server 2008+ **移除 `allow updates` 選項**，改用 `ALTER DATABASE DBName SET EMERGENCY;`。

步驟 3：單用戶模式執行 DBCC 修復


-- 重啟服務生效 EMERGENCY 模式 (或直接 ALTER DATABASE)
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB('DBName', REPAIR_ALLOW_DATA_LOSS); -- 最後手段，可能遺失資料
GO

步驟 4：還原資料庫狀態


-- SQL 2000/2005
UPDATE sysdatabases SET status = 28 WHERE name = 'DBName'; -- 28 = 正常線上
EXEC sp_configure 'allow updates', 0;
RECONFIGURE WITH OVERRIDE;
GO

-- SQL 2008+
ALTER DATABASE DBName SET MULTI_USER;
ALTER DATABASE DBName SET ONLINE;

步驟 5：資料遷移重建 (關鍵：確保資料完整性)

EMERGENCY 模式下資料庫唯讀且不保證交易一致性。**必須立即匯出資料到全新資料庫**：

建立新資料庫 `NEW_DB`。
使用 **SSIS / DTS 匯入匯出精靈** 或 `bcp` / `INSERT INTO ... SELECT * FROM OPENROWSET` 將 `DBName` 所有物件 (表、檢視、預存程序、資料) 複製到 `NEW_DB`。
驗證 `NEW_DB` 資料完整性、應用程式測試通過。
重新命名/切換：`DBName` → `DBName_Old`，`NEW_DB` → `DBName`。

四、關鍵風險提醒與預防措施

風險	說明
資料遺失	`REPAIR_ALLOW_DATA_LOSS` 會刪除損壞頁面/索引，不可逆。
交易不一致	EMERGENCY 模式繞過復原，未提交交易可能殘留、已提交交易可能遺失。
系統表更新危險	直接 UPDATE `sysdatabases` 極度危險，僅限極端災難修復，且 SQL 2008+ 已封鎖。
預防勝於治療	記錄檔放置於獨立磁碟/RAID 1+0，與資料檔隔離。啟用完整復原模式 + 高頻記錄備份 (15-30 分鐘)。監控 `log_reuse_wait_desc` 防止記錄暴增填滿磁碟導致自動截斷/遺失。定期 `DBCC CHECKDB` 早期發現損壞。