萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 資料庫崩潰災難恢復實戰:從附加單檔到重建日誌、DBCC 修復全流程

SQL Server 崩潰不慌:系統化災難恢復 SOP 讓資料起死回生

資料庫崩潰 (硬體故障、電源中斷、惡意刪除) 時,若備份缺失或過舊,需採用緊急恢復手段。本文按成功率遞減順序,詳細記錄從 sp_attach_single_file_db 到 緊急模式重建日誌 → DBCC 修復 → 重建索引/物件 的完整實戰流程。

理想情況:MDF+LDF 完好 → 直接附加

EXEC sp_attach_db @dbname = 'MyDB',
    @filename1 = 'D:\Data\MyDB.mdf',
    @filename2 = 'D:\Data\MyDB_log.ldf';

-- 或僅有 MDF
EXEC sp_attach_single_file_db @dbname = 'MyDB',
    @physname = 'D:\Data\MyDB.mdf';

情況一:sp_attach_single_file_db 嘗試 (需近期有 CHECKPOINT)

EXEC sp_attach_single_file_db 'MyDB', 'D:\Data\MyDB.mdf';
  • 成功條件:資料庫乾淨關閉 (有 Checkpoint),LDF 可自動重建。
  • 失敗常見錯誤:Device activation error... log file not found、CREATE DATABASE aborted。

情況二:緊急模式重建日誌 (核心救命技)

適用:MDF 存在、LDF 遺損/損壞、無可用備份、無法附加。

步驟 1:允許系統表更新 (舊版需求,新版慎用)

USE master;
GO
EXEC sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
GO

警告:SQL Server 2005+ 強烈不建議 直接更新系統表,極易導致元數據損壞。現代版本請改用 ALTER DATABASE ... SET EMERGENCY。

步驟 2 (現代做法):設定 EMERGENCY 模式 + 單用戶 + 重建日誌

-- 1. 進入緊急模式 (唯讀、僅 sysadmin 可存取、不啟動恢復)
ALTER DATABASE MyDB SET EMERGENCY;
GO

-- 2. 單用戶模式 (排他存取)
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- 3. 重建日誌 (會丟失未提交事務,破壞事務一致性)
DBCC CHECKDB ('MyDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
-- 或針對性重建日誌 (舊版語法,新版已整合進 CHECKDB REPAIR)
-- DBCC REBUILD_LOG('MyDB', 'D:\Data\MyDB_log.ldf');

-- 4. 驗證物理一致性
DBCC CHECKDB ('MyDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

-- 5. 恢復正常模式
ALTER DATABASE MyDB SET MULTI_USER;
GO

-- 6. 關閉系統表更新 (若步驟1開啟)
EXEC sp_configure 'allow updates', 0;
RECONFIGURE WITH OVERRIDE;
GO

情況三:深度修復 — DBCC CHECKTABLE 逐表檢查

-- 取得所有使用者表
SELECT 'DBCC CHECKTABLE (''' + name + ''') WITH NO_INFOMSGS, ALL_ERRORMSGS;'
FROM sys.objects
WHERE type = 'U' AND is_ms_shipped = 0;
-- 複製輸出逐條執行,或動態 SQL 迴圈執行

若仍報錯,嘗試 SELECT * INTO new_tbl FROM corrupt_tbl 導出乾淨資料,重建表。

善後重建

  1. 重建所有索引:DBCC DBREINDEX (舊版) / ALTER INDEX ALL ON tbl REBUILD (新版)。
  2. 重建視圖、預存程序、觸發器 (從版控/備份還原)。
  3. 更新統計資訊:EXEC sp_updatestats。
  4. 驗證應用功能、備份新資料庫。

關鍵風險與接受度

操作資料損失風險業務接受度建議
sp_attach_single_file_db低 (需乾淨關閉)首選
EMERGENCY + REPAIR_ALLOW_DATA_LOSS中高 (未提交事務丟失、可能刪除損壞頁)無備份時最後手段,需業務簽署風險接受書
SELECT INTO 導出乾淨資料低 (僅丟失損壞頁)配合 REPAIR 使用

預防勝於治療:備份鐵律

  • 全量備份 (每日) + 差異/日誌備份 (每小時/15 分鐘)。
  • 備份檔異地存儲、定期校驗 RESTORE VERIFYONLY、季度演練恢復。
  • 啟用 page_verify CHECKSUM、backup checksum。
  • 監控 suspect_pages、I/O 錯誤、磁碟健康 (SMART)。
copyright © 萬盛學電腦網 all rights reserved