SQL Server 使用者自定義資料庫修復實戰：重做日誌應用、非歸檔模式恢復與時間點還原

資料庫修復是 DBA 面對介質故障、邏輯錯誤、惡意破壞時的核心技能。本文系統闡述 SQL Server 環境下的三大修復體系：自動重做日誌應用、非歸檔模式 (Simple Recovery) 介質恢復、時間點還原，並提供標準化操作腳本與決策流程圖。

修復策略決策樹

發現資料庫損壞/數據遺失
       │
       ▼
評估復原模式: FULL / BULK_LOGGED / SIMPLE
       │
       ├── FULL / BULK_LOGGED ──→ 是否有完整備份鏈 + 記錄備份鏈 完整？
       │         ├─ 是 ──→ 時間點還原 (Point-in-Time Recovery) [首選]
       │         └─ 否 ──→ 檢查尾部記錄備份 → 可能需容忍數據遺失
       │
       └── SIMPLE ──→ 僅能恢復至最近一次完整/差異備份點
                 (無記錄備份，無法時間點還原)

方案一：自動重做日誌應用 (適用於完整/大量記錄模式)

前提：資料庫檔案完好，僅需將交易記錄重放至一致性狀態

方法 A：SET AUTORECOVERY ON (舊版語法，相容性保留)

-- 1. 啟動實例並掛載資料庫 (不復原)
STARTUP MOUNT;  -- 實際對應: RESTORE DATABASE [DB] WITH NORECOVERY;

-- 2. 啟用自動重做日誌應用
SET AUTORECOVERY ON;  -- 實際對應: 資料庫屬性 → 選項 → 自動關閉/自動收縮 相關設定

-- 3. 執行恢復 (重放所有記錄至最新)
RECOVER DATABASE;  -- 實際對應: RESTORE DATABASE [DB] WITH RECOVERY;

-- 4. 開啟資料庫供存取
ALTER DATABASE [DB] SET ONLINE;  -- 隱含在 WITH RECOVERY 中

方法 B：RECOVER AUTOMATIC (簡化語法)

STARTUP MOUNT;
RECOVER AUTOMATIC DATABASE;  -- 等同於 SET AUTORECOVERY ON + RECOVER DATABASE
ALTER DATABASE [DB] SET ONLINE;

現代 SQL Server 對應操作 (T-SQL 標準語法)

-- 完整恢復流程：完整備份 → 差異備份 → 記錄備份鏈 → 目標時間點
RESTORE DATABASE [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Full.bak'
WITH NORECOVERY, REPLACE, STATS=5;  -- 完整備份

RESTORE DATABASE [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Diff.bak'
WITH NORECOVERY, STATS=5;           -- 最新差異備份 (可選)

RESTORE LOG [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Log_1.trn'
WITH NORECOVERY, STATS=5;           -- 記錄備份 1

RESTORE LOG [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Log_2.trn'
WITH NORECOVERY, STATS=5;           -- 記錄備份 2
-- ... 依序應用所有記錄備份 ...

RESTORE LOG [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Log_Tail.trn'  -- 尾部記錄備份 (關鍵！)
WITH RECOVERY, STOPAT = '2024-01-15 14:30:00', STATS=5;  -- 時間點還原

方案二：非歸檔模式 (SIMPLE) 介質恢復

特點：無記錄備份，記錄自動截斷，僅能恢復至備份時間點，RPO = 備份間隔

場景 A：原位置恢復 (檔案路徑未變)

-- 1. 關閉資料庫 (若仍在線)
ALTER DATABASE [TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 2. 從備份還原 (覆蓋現有檔案)
RESTORE DATABASE [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Full.bak'
WITH REPLACE, RECOVERY, STATS=5;
-- RECOVERY 參數直接完成重做/回滾並上線

場景 B：新位置恢復 (檔案路徑變更/磁碟損壞遷移)

-- 1. 關閉資料庫
ALTER DATABASE [TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 2. 還原至新路徑 (使用 MOVE 指定新檔案位置)
RESTORE DATABASE [TargetDB]
FROM DISK = 'D:\Backup\TargetDB_Full.bak'
WITH REPLACE, RECOVERY, STATS=5,
MOVE 'TargetDB_Data' TO 'E:\NewData\TargetDB.mdf',
MOVE 'TargetDB_Log'  TO 'F:\NewLog\TargetDB_Log.ldf';

-- 3. 驗證
DBCC CHECKDB([TargetDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

進階修復技術：頁面級恢復與檔案群組恢復

頁面級恢復 (Page Restore) —— 針對少數損壞頁面，避免全庫還原

-- 1. 識別損壞頁面 (來自 DBCC CHECKDB 或 錯誤日誌 823/824/825)
-- 假設 FileID=1, PageID=12345 和 FileID=1, PageID=12346 損壞

-- 2. 執行頁面恢復 (Enterprise 版專屬)
RESTORE DATABASE [TargetDB]
PAGE = '1:12345', '1:12346'
FROM DISK = 'D:\Backup\TargetDB_Full.bak'
WITH NORECOVERY;

-- 3. 應用後續記錄備份至最新
RESTORE LOG [TargetDB] FROM DISK = '...' WITH NORECOVERY;
-- ... 所有記錄備份 ...

-- 4. 完成恢復
RESTORE LOG [TargetDB] FROM DISK = '...Tail.trn' WITH RECOVERY;

檔案群組恢復 —— 僅恢復受損檔案群組，核心業務檔案群組保持在線

-- 假設次要檔案群組 FG_Archive 受損，主檔案群組 PRIMARY 正常
RESTORE DATABASE [TargetDB] FILEGROUP = 'FG_Archive'
FROM DISK = 'D:\Backup\TargetDB_FG_Archive.bak'
WITH NORECOVERY;

RESTORE LOG [TargetDB] FROM DISK = '...' WITH NORECOVERY;
-- ... 應用記錄至最新 ...

RESTORE LOG [TargetDB] FROM DISK = '...Tail.trn' WITH RECOVERY;
-- 期間 PRIMARY 檔案群組保持 ONLINE 可存取

修復後驗證標準作業程序

避坑指南：修復過程中常見致命錯誤

1. 忘記尾部記錄備份：導致最後交易遺失，RPO 失守。鐵律：恢復前必須 BACKUP LOG [DB] TO DISK = '..._Tail.trn' WITH NORECOVERY;
2. 記錄備份鏈中斷：中間遺失 .trn 檔案。對策：只能恢復至缺口前，後續數據永久遺失。
3. REPLACE 覆蓋錯誤資料庫：誤覆蓋健康副本。對策：執行前 SELECT * FROM sys.master_files WHERE database_id = DB_ID('TargetDB') 確認檔案路徑。
4. 忽略檔案群組只讀狀態：唯讀檔案群組無需恢復，但必須顯式聲明 WITH PARTIAL。

DBA 鐵律：「備份未測試，等於沒備份；修復未演練，等於不會修。」每季度必須在非生產環境完整演練：完整恢復、時間點恢復、頁面恢復、檔案群組恢復，記錄 RTO 實測值並優化流程。