SQL Server 資料庫崩潰災難恢復實戰：附加資料庫、重建記錄檔、緊急模式、DBCC 修復全流程

理想情況：資料/記錄檔完好

-- 僅附加資料檔
EXEC sp_attach_db @dbname = 'MyDB', @filename1 = 'D:\Data\MyDB.mdf';
-- 或停機複製所有檔案 (含 master) 回原路徑 (不建議)

常見情況：記錄檔損毀/遺失，無可用備份

存儲子系統故障常導致記錄檔不可用，且無近期備份。嘗試以下階梯式恢復：

步驟 1：嘗試單檔附加 (需曾執行 CHECKPOINT)

EXEC sp_attach_single_file_db @dbname = 'MyDB', @physname = 'D:\Data\MyDB.mdf';

步驟 2：緊急模式重建記錄檔 (核心技巧)

-- 1. 允許系統表更新
USE master;
EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;

-- 2. 設定資料庫為 EMERGENCY 模式 (status = 32768)
UPDATE sysdatabases SET status = 32768 WHERE name = 'MyDB';

-- 3. 重建記錄檔 (系統會自動建立新 log)
-- 無報錯即成功

-- 4. 重啟 SQL Server 服務
-- 5. 單用戶模式 + DBCC 檢查
EXEC sp_dboption 'MyDB', 'single user', 'true';
DBCC CHECKDB('MyDB');

-- 6. 修復錯誤 (視嚴重度)
DBCC CHECKDB('MyDB', REPAIR_REBUILD);          -- 輕微
DBCC CHECKDB('MyDB', REPAIR_ALLOW_DATA_LOSS);   -- 嚴重，可能遺失資料

-- 7. 逐表深度檢查
DECLARE @tbl sysname;
DECLARE cur CURSOR FOR 
    SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1;
OPEN cur;
FETCH NEXT FROM cur INTO @tbl;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC CHECKTABLE(@tbl);
    FETCH NEXT FROM cur INTO @tbl;
END
CLOSE cur; DEALLOCATE cur;

-- 8. 重建索引/視圖/SP/Trigger
DBCC DBREINDEX('MyTable', '', 80);

-- 9. 恢復資料庫狀態
UPDATE sysdatabases SET status = 28 WHERE name = 'MyDB';  -- 或正常狀態值
EXEC sp_resetstatus 'MyDB';
EXEC sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;

關鍵提醒

• 進行中交易必定遺失：緊急模式下無法回滾未提交交易
• REPAIR_ALLOW_DATA_LOSS 為最後手段，**務必先備份 .mdf 複本**
• 系統表直更新需 **SQL 2000/2005 相容模式**，SQL 2008+ 已移除 allow updates，改用 ALTER DATABASE ... SET EMERGENCY
• 現代版本 (2012+) 首選：ALTER DATABASE MyDB SET EMERGENCY; ALTER DATABASE MyDB SET SINGLE_USER; DBCC CHECKDB ...

預防勝於治療

1. 完整備份 + 差異備份 + 記錄備份** 定期驗證還原
2. 啟用 **頁面驗證 CHECKSUM** + **備份壓縮/加密**
3. 儲存層 **RAID 1/10 + BBWC + 定期巡檢**
4. 監控 suspect_pages、msdb..backupmediafamily、磁碟健康度