SQL Server 三大復原模式完整解析：完整、大量記錄與簡單模式的備份策略差異

復原模式決定備份/還原能力與記錄檔管理

每個資料庫必須設定三種復原模式之一，透過 ALTER DATABASE ... SET RECOVERY 切換 (需獨佔存取或單一使用者模式)。

模式比較總表

切換模式影響

• FULL → BULK_LOGGED：大量作業期間切換，完成後立即切回 FULL 並立即執行記錄備份，確保復原鏈完整。
• FULL → SIMPLE：斷開記錄備份鏈，後續無法時間點還原至切換前。切換前務必執行完整備份。
• SIMPLE → FULL：啟動完整記錄，**必須立即執行完整備份** 才能啟動記錄備份鏈，否則記錄備份失敗 (錯誤 4214)。

BACKUP 指令核心語法

-- 完整資料庫備份至磁碟檔
BACKUP DATABASE [Northwind] TO DISK = N'C:\Backup\Northwind_Full.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;

-- 交易記錄備份 (僅 FULL/BULK_LOGGED)
BACKUP LOG [Northwind] TO DISK = N'C:\Backup\Northwind_Log.trn'
WITH NOINIT, COMPRESSION, CHECKSUM;

-- 差異備份
BACKUP DATABASE [Northwind] TO DISK = N'C:\Backup\Northwind_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- 檔案/檔案組備份
BACKUP DATABASE [Northwind] FILEGROUP = 'SalesFG' TO DISK = N'C:\Backup\SalesFG.bak';

邏輯備份裝置 (管理多組備份組)

-- 建立邏輯裝置
EXEC sp_addumpdevice 'disk', 'Northwind_Backups', 'C:\Backup\Northwind_Multi.bak';

-- 使用邏輯裝置備份 (可累積多組)
BACKUP DATABASE [Northwind] TO Northwind_Backups;
BACKUP LOG [Northwind] TO Northwind_Backups;

-- 查看裝置內容
RESTORE HEADERONLY FROM Northwind_Backups;

大型資料庫備份策略建議

1. 檔案組備份輪替：將歷史靜態資料歸檔至唯讀檔案組 (READ_ONLY)，僅備份讀寫檔案組，大幅縮減備份窗口。
2. 差異備份鏈：每日完整 + 每小時差異 + 每 15 分鐘記錄。還原時：完整 → 最新差異 → 後續所有記錄。
3. 壓縮與校驗和：WITH COMPRESSION, CHECKSUM 節省儲存/頻寬、偵測媒體損壞。
4. 備份驗證自動化：維護計畫排程 RESTORE VERIFYONLY 或第三方工具 (Redgate SQL Backup, Commvault, Veeam) 定期還原測試。