MSSQL 備份與還原完整指南：從恢復模型到實戰語法

核心概念：三大資料庫恢復模型

SQL Server 提供三種恢復模型，決定了交易記錄的管理方式與資料保護層級，是制定備份策略的基石。

1. 完整恢復模型

• 機制：完整記錄所有操作，包含大量操作。備份時需同時備份資料檔與記錄檔。
• 還原能力：支援「時間點還原」，可將資料庫恢復到任意指定時刻，實現零資料遺失（RPO=0）。
• 代價：記錄檔增長迅速，佔用空間大，略微影響效能。必須定期進行記錄備份以截斷記錄檔。

2. 大量記錄恢復模型

• 機制：對大量操作（如 BULK INSERT、SELECT INTO、索引建立）採用最小記錄，僅記錄結果而非細節。
• 還原能力：支援還原到記錄備份的結尾，不支援時間點還原。若大量操作後記錄檔損壞，該區間資料需重做。
• 適用場景：大量資料匯入期間臨時切換，以提升效能並節省記錄空間，事後應切回完整模式。

3. 簡單恢復模型

• 機制：自動截斷記錄檔（Checkpoint 時），不支援記錄備份。僅備份資料檔。
• 還原能力：僅能還原到最後一次完整/差異備份完成時刻，資料遺失風險最大。
• 適用場景：測試環境、唯讀資料庫、可容忍資料遺失的非生產系統。不建議用於生產環境核心業務。

-- 切換恢復模型範例
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
ALTER DATABASE [YourDB] SET RECOVERY BULK_LOGGED;
ALTER DATABASE [YourDB] SET RECOVERY FULL;

備份設備與媒體集管理

邏輯備份設備

建議使用永久邏輯設備，便於腳本維護與排程自動化。

-- 建立本地磁碟永久備份設備
EXEC sp_addumpdevice 'disk', 'Bak_Device_Local', 'E:\Backup\DB_Backup.bak';
-- 建立網路路徑永久備份設備
EXEC sp_addumpdevice 'disk', 'Bak_Device_Network', '\\BackupServer\Share\DB_Backup.bak';
-- 刪除設備
EXEC sp_dropdevice 'Bak_Device_Network';

媒體集與多檔案平行備份

將備份分散至多個檔案（置於不同磁碟/磁帶），可顯著提升 I/O 吞吐量。媒體集內的檔案必須同時使用，不可單獨讀寫。

-- 初始化媒體集並平行備份至 3 個檔案
BACKUP DATABASE [DB_Name] TO Bak_File1, Bak_File2, Bak_File3
WITH MEDIANAME = 'MediaSet_DB', FORMAT;

-- 後續備份必須指定所有媒體集成員
BACKUP DATABASE [DB_Name] TO Bak_File1, Bak_File2, Bak_File3;

-- 查看媒體集內容
RESTORE HEADERONLY FROM Bak_File1, Bak_File2, Bak_File3;

三大核心備份策略

1. 完整備份 - 基石

包含所有資料頁面與作用中交易的記錄部分。所有差異與記錄備份的基準。

BACKUP DATABASE [DB_Name] TO Bak_Device WITH INIT, NAME = 'Full_Backup_Base';

2. 差異備份 - 加速恢復

僅備份自上次完整備份以來變更的資料頁（透過 DCM 頁面追蹤）。恢復時：最新完整備份 + 最新差異備份。

BACKUP DATABASE [DB_Name] TO Bak_Device WITH DIFFERENTIAL, NAME = 'Diff_Backup_20231027';

3. 交易記錄備份 - 實現時間點還原

僅在完整/大量記錄模式下可用。記錄備份鏈不可中斷。恢復流程：完整備份 (NORECOVERY) → 依序套用所有記錄備份 (NORECOVERY) → 最後一筆記錄備份 (RECOVERY/STOPAT)。

BACKUP LOG [DB_Name] TO Bak_Device WITH NAME = 'Log_Backup_1000';

記錄檔管理與緊急處理

自動截斷設定

-- 簡單模式下自動截斷 (預設行為)
-- 完整模式下需定期 BACKUP LOG 才會截斷

緊急截斷指令 (謹慎使用，會破壞記錄鏈)

• BACKUP LOG [DB] WITH TRUNCATE_ONLY：截斷並記錄操作 (舊版本)。
• BACKUP LOG [DB] WITH NO_LOG：截斷但不記錄操作 (舊版本)。
• BACKUP LOG [DB] TO Bak_Device WITH NO_TRUNCATE：關鍵救命稻草。資料檔損壞時，在記錄檔仍在線的情況下強制備份尾部記錄，確保零資料遺失。

實戰恢復範例：時間點還原

-- 1. 發生誤操作前，立即備份尾部記錄 (若資料庫在線)
BACKUP LOG [DB_Name] TO DISK = 'E:\Backup\TailLog.trn' WITH NO_TRUNCATE;

-- 2. 還原完整備份
RESTORE DATABASE [DB_Name] FROM DISK = 'E:\Backup\Full.bak' WITH NORECOVERY, REPLACE;

-- 3. 還原差異備份 (若有)
RESTORE DATABASE [DB_Name] FROM DISK = 'E:\Backup\Diff.bak' WITH NORECOVERY;

-- 4. 依序還原記錄備份至誤操作前一刻
RESTORE LOG [DB_Name] FROM DISK = 'E:\Backup\Log1.trn' WITH NORECOVERY;
RESTORE LOG [DB_Name] FROM DISK = 'E:\Backup\Log2.trn' WITH NORECOVERY;
RESTORE LOG [DB_Name] FROM DISK = 'E:\Backup\TailLog.trn' WITH RECOVERY, STOPAT = '2023-10-27 14:30:00';