SQL Server 2008 資料庫備份、還原與災難恢復完整實戰指南

備份類型與策略

圖形介面操作 (SSMS)

1. 右鍵資料庫 → **工作 → 備份**。
2. **備份類型**選擇：完整/差異/交易記錄。
3. **目的地**：磁碟 → 新增 `.bak`/`.trn` 檔案路徑 (建議含日期時間命名)。
4. **媒體選項**：覆寫/附加、驗證備份完整性 (建議勾選)、壓縮備份 (2008+ Enterprise/Standard 支援)。

T-SQL 指令碼化備份 (便於排程/自動化)

完整備份

BACKUP DATABASE [TestDB] 
TO DISK = N'D:\Backup\TestDB_Full_20240315.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10, COMPRESSION;

交易記錄備份

BACKUP LOG [TestDB] 
TO DISK = N'D:\Backup\TestDB_Log_20240315_1400.trn'
WITH NOFORMAT, INIT, NAME = N'TestDB-Log', SKIP, NOREWIND, NOUNLOAD, STATS = 10, COMPRESSION;

還原流程

1. 右鍵資料庫 → **工作 → 還原 → 資料庫**。
2. **來源**：裝置 → 選擇 `.bak` 檔案，SSMS 自動列出備份集時間線。
3. **目的地**：確認資料庫名稱、檔案重置路徑 (如需異地還原)。
4. **選項**： - 復原狀態：`RESTORE WITH RECOVERY` (可立即使用) / `NORECOVERY` (套用後續記錄備份) / `STANDBY` (唯讀待命)。

時間點還原

-- 1. 完整備份還原 (NORECOVERY)
RESTORE DATABASE [TestDB] FROM DISK = 'Full.bak' WITH NORECOVERY, REPLACE, STATS=10;

-- 2. 最新差異備份還原 (NORECOVERY) - 可選
RESTORE DATABASE [TestDB] FROM DISK = 'Diff.bak' WITH NORECOVERY, STATS=10;

-- 3. 依序還原記錄備份至目標時間點
RESTORE LOG [TestDB] FROM DISK = 'Log_1.trn' WITH NORECOVERY;
RESTORE LOG [TestDB] FROM DISK = 'Log_2.trn' WITH STOPAT = '2024-03-15 14:30:00', RECOVERY;

記錄檔收縮與維護 (避免 LDF 失控)

• **根因修正**：建立定期記錄備份作業 (Agent Job)，確保 VLF 定期截斷。
• **緊急收縮** (僅限簡單復原模式下)：

  ALTER DATABASE [db] SET RECOVERY SIMPLE;
  DBCC SHRINKFILE (N'db_log', 1024, TRUNCATEONLY);
  ALTER DATABASE [db] SET RECOVERY FULL;
  -- 立即執行完整備份 + 記錄備份重建鏈

• **監控**：`DBCC LOGINFO` 觀察 VLF 狀態 (Status=0 可重用, 2=作用中)；`sys.dm_db_log_space_usage` (2012+)。

LSN 鏈與恢復點關鍵知識

• 每次備份記錄 **First LSN / Last LSN / Checkpoint LSN**。
• 記錄備份的 First LSN 必須 ≤ 前一次備份的 Last LSN，形成連續鏈。
• 若中斷鏈 (如切換復原模式、手動截斷)，必須重新完整備份啟動新鏈，否則無法時間點還原。

加密資料庫還原注意事項

• 還原 TDE 加密資料庫需具備對應 **憑證/非對稱金鑰** 及備份檔案。
• 先還原/建立主金鑰 → 建立憑證 → 還原資料庫。