SQL Server 資料庫備份與還原完整策略指南

SQL Server 備份還原核心架構與最佳實踐

資料庫備份是災難恢復的最後防線。完整的備份策略包含完整備份、差異備份、交易記錄備份三層防護，配合正確的還原序列，能將 RPO/RTO 降至最低。

三大備份類型對照

復原模式選擇

• 簡單復原模式：記錄自動截斷，不支援記錄備份，無法時間點還原
• 完整復原模式：完整記錄所有操作，支援記錄備份與時間點還原（生產環境標準）
• 大量記錄復原模式：大量操作最小記錄，效能較好但無法精確時間點還原

標準備份策略範例

-- 策略：每日完整 + 每6小時差異 + 每15分鐘記錄

-- 1. 完整備份 (每日 02:00)
BACKUP DATABASE [MyDB] 
TO DISK = 'D:\Backup\MyDB_Full_20240115.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10

-- 2. 差異備份 (06:00, 12:00, 18:00)
BACKUP DATABASE [MyDB] 
TO DISK = 'D:\Backup\MyDB_Diff_20240115_0600.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM

-- 3. 記錄備份 (每15分鐘)
BACKUP LOG [MyDB] 
TO DISK = 'D:\Backup\MyDB_Log_20240115_0615.trn'
WITH COMPRESSION, CHECKSUM

還原序列實戰

場景一：完整還原至最新時間點

-- 1. 還原完整備份 (NORECOVERY 保持還原狀態)
RESTORE DATABASE [MyDB] 
FROM DISK = 'D:\Backup\MyDB_Full_20240115.bak'
WITH NORECOVERY, REPLACE, STATS = 10

-- 2. 還原最新差異備份
RESTORE DATABASE [MyDB] 
FROM DISK = 'D:\Backup\MyDB_Diff_20240115_1800.bak'
WITH NORECOVERY, STATS = 10

-- 3. 依序還原所有記錄備份
RESTORE LOG [MyDB] 
FROM DISK = 'D:\Backup\MyDB_Log_20240115_1815.trn'
WITH NORECOVERY

-- ... 依序套用至最後一份記錄備份 ...

-- 4. 完成還原 (RECOVERY 上線)
RESTORE DATABASE [MyDB] WITH RECOVERY

場景二：時間點還原 (STOPAT)

RESTORE LOG [MyDB] 
FROM DISK = 'D:\Backup\MyDB_Log_20240115_1815.trn'
WITH NORECOVERY, STOPAT = '2024-01-15 18:10:00'

-- 後續記錄備份不再套用，直接 RECOVERY

場景三：頁面級還原 (線上修復損壞頁)

RESTORE DATABASE [MyDB] 
PAGE = '1:123, 1:456'  -- file_id:page_id
FROM DISK = 'D:\Backup\MyDB_Full_20240115.bak'
WITH NORECOVERY
-- 再套用差異 + 記錄備份至最新

常見問題與解決方案

問題：記錄檔暴增、磁碟空間不足

• 確認復原模式為「完整」但未做記錄備份 → 立即建立記錄備份作業
• 長時間交易阻塞記錄截斷 → 監控 `DBCC OPENTRAN`
• 可用性群組/鏡像同步延遲 → 檢查次要複本狀態

問題：還原失敗「媒體集有 2 個媒體家族」

備份指令寫入多個檔案，還原時需同時指定所有檔案：

RESTORE DATABASE [MyDB] 
FROM DISK = 'D:\Backup\MyDB_1.bak', DISK = 'D:\Backup\MyDB_2.bak'
WITH REPLACE

問題：備份檔案損壞/校驗失敗

• 備份時加 `CHECKSUM`、`VERIFYONLY` 驗證
• 定期 `RESTORE VERIFYONLY FROM DISK = ...` 測試備份可用性
• 異地備份、雲端備份 (Azure Blob、S3) 實現 3-2-1 原則

自動化與監控建議

1. 使用 Maintenance Plan 或 Ola Hallengren 腳本自動化備份
2. 監控 `msdb.dbo.backupset` 確認備份成功率
3. 設定警示：備份失敗、記錄檔增長異常、備份檔案超齡
4. 每季演練完整還原流程，驗證 RTO