SQL Server 資料庫崩潰災難恢復完整實戰手冊

資料庫崩潰無法完全避免，尤其缺乏專職 DBA、備份過舊或無備份時，恢復難度劇增。本文提供從輕度到重度的分級恢復方案，含實戰指令與注意事項。

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

-- 附加資料庫
EXEC sp_attach_db 'DBName', '路徑\Data.mdf', '路徑\Log.ldf'

或停止服務後複製所有檔案（含 master）回原路徑，但推薦 sp_attach_db。

常見情況：記錄檔損壞/遺失，唯有 MDF 檔案

方案 1：sp_attach_single_file_db

EXEC sp_attach_single_file_db 'DBName', '路徑\Data.mdf'

若資料庫剛好執行過 CHECKPOINT，可能成功自動重建記錄檔。

方案 2：緊急模式重建記錄檔（核心實戰步驟）

1. 允許系統表更新

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

2. 設定資料庫為緊急模式（status = 32768）

   UPDATE sysdatabases SET status = 32768 WHERE name = 'DBName'
   GO

3. 重建記錄檔

   DBCC REBUILD_LOG('DBName', '路徑\NewLog.ldf')
   GO

   ⚠️ 若報錯「無法排他鎖定」，關閉所有連線（含 SSMS 物件瀏覽器）後重試。

4. 驗證與修復

   -- 單用戶模式
   EXEC sp_dboption 'DBName', 'single user', 'true'
   GO
   
   -- 檢查資料庫一致性
   DBCC CHECKDB('DBName')
   GO
   
   -- 若有錯誤，嘗試修復（可能遺失資料）
   DBCC CHECKDB('DBName', REPAIR_ALLOW_DATA_LOSS)
   GO

5. 逐表檢查

   -- 找出所有使用者表
   SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
   
   -- 逐一執行
   DBCC CHECKTABLE('TableName')
   GO

6. 恢復狀態與清理

   -- 恢復資料庫狀態（視原狀態調整，或用 sp_resetstatus）
   UPDATE sysdatabases SET status = 28 WHERE name = 'DBName'
   GO
   
   -- 關閉系統表更新
   EXEC sp_configure 'allow updates', 0
   RECONFIGURE WITH OVERRIDE
   GO
   
   -- 重建索引、檢視、預存程序等
   DBCC DBREINDEX('TableName')
   GO

無備份、無 MDF、無 LDF：只能靠天意

若連 MDF 檔案都遺失，且無任何備份，資料基本無法恢復。定期備份是唯一保命符。

實戰心得總結

• ✅ 先試 sp_attach_single_file_db（成本最低）
• ✅ 緊急模式 + DBCC REBUILD_LOG 成功率極高
• ⚠️ 進行中交易必定遺失，資料可能有損壞
• ✅ 必做 DBCC CHECKDB + CHECKTABLE 深度掃描
• ✅ 最後重建所有索引與物件
• 🔔 事後檢討備份策略，設定定期完整/差異/記錄備份