備份策略設計原則
| 原則 | 說明 | 典型設定 |
|---|
| RPO (資料遺失容忍度) | 決定記錄備份頻率 | 核心庫 15 分鐘、一般庫 1 小時 |
| RTO (恢復時間目標) | 決定完整/差異備份頻率與儲存介質 | 完整備份每日、差異每 6 小時 |
| 3-2-1 原則 | 3 份副本、2 種介質、1 份異地 | 本地磁碟 + NAS + 雲端/異地磁帶 |
| 驗證與演練 | 未測試的備份 = 沒有備份 | 每季隨機還原演練、Checksum 驗證 |
備份類型與 T-SQL 範本
完整備份 (Full)
BACKUP DATABASE [DBName]
TO DISK = N'D:\Backup\DBName_FULL_20240115.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 5, FORMAT;
GO
差異備份 (Differential)
BACKUP DATABASE [DBName]
TO DISK = N'D:\Backup\DBName_DIFF_20240115_0600.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 5, DIFFERENTIAL;
GO
交易記錄備份 (Log)
BACKUP LOG [DBName]
TO DISK = N'D:\Backup\DBName_LOG_20240115_0615.trn'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 5;
GO
檔案/檔案組備份 (大型資料庫分片)
BACKUP DATABASE [DBName] FILEGROUP = 'FG_Archive'
TO DISK = N'D:\Backup\DBName_FG_Archive.bak'
WITH INIT, COMPRESSION, CHECKSUM;
GO
還原策略與實戰流程
完整還原 (最新完整 + 最新差異 + 所有後續記錄)
-- 1. 尾記錄備份 (若記錄檔仍可存取)
BACKUP LOG [DBName] TO DISK = 'D:\Backup\DBName_TAIL.trn' WITH NORECOVERY, INIT;
-- 2. 還原完整備份
RESTORE DATABASE [DBName]
FROM DISK = 'D:\Backup\DBName_FULL_20240115.bak'
WITH NORECOVERY, REPLACE,
MOVE 'DBName' TO 'D:\Data\DBName.mdf',
MOVE 'DBName_Log' TO 'D:\Log\DBName_Log.ldf',
STATS = 5;
-- 3. 還原最新差異備份
RESTORE DATABASE [DBName]
FROM DISK = 'D:\Backup\DBName_DIFF_20240115_0600.bak'
WITH NORECOVERY, STATS = 5;
-- 4. 依序還原所有記錄備份
RESTORE LOG [DBName] FROM DISK = 'D:\Backup\DBName_LOG_20240115_0615.trn' WITH NORECOVERY;
RESTORE LOG [DBName] FROM DISK = 'D:\Backup\DBName_LOG_20240115_0630.trn' WITH NORECOVERY;
-- ...
-- 5. 恢復資料庫
RESTORE DATABASE [DBName] WITH RECOVERY;
時間點還原 (STOPAT)
RESTORE LOG [DBName] FROM DISK = '...'
WITH NORECOVERY, STOPAT = '2024-01-15 14:30:00';
頁面級還原 (Page Restore, Enterprise 版)
RESTORE DATABASE [DBName] PAGE = '1:1000, 1:1001'
FROM DISK = '...' WITH NORECOVERY;
-- 後續套用記錄備份至最新
自動化維護計畫 (Maintenance Plan) 完整部署
精靈建立標準計畫
- SSMS → 管理 → 維護計畫 → 右鍵「維護計畫精靈」。
- 選擇資料庫 → 任務：
✅ 檢查資料庫完整性 (DBCC CHECKDB)
✅ 重建/重組索引 (依碎片率閾值)
✅ 更新統計資料
✅ 完整備份 (每日)
✅ 差異備份 (每 6 小時)
✅ 記錄備份 (每 15 分鐘)
✅ 清除舊備份 (保留 7 天)
✅ 清除維護歷程記錄。
- 排程：子計畫獨立排程，避免衝突。
- 報表：寫入檔案/郵件/資料表，失敗發送警示。
進階：SSIS 維護計畫 (SQL 2012+)
- 部署至 SSISDB，支援參數化、環境變數、版本控制、專案級記錄。
- 可整合自定義 Script Task：異地複製、雲端上傳、驗證還原測試。
收縮資料庫與記錄檔：正確姿勢
⚠️ 常態收縮是反模式（造成碎片、記錄檔再次增長、效能抖動）。僅在以下情況一次性執行：
- 一次性大量刪除/截斷釋放大量空間。
- 記錄檔因長時間無記錄備份暴增，已改為定期記錄備份後需釋放空間。
安全收縮步驟
-- 1. 確認復原模式為簡單 (或已完成記錄備份)
ALTER DATABASE [DBName] SET RECOVERY SIMPLE;
-- 2. 針對記錄檔收縮至目標大小 (MB)
DBCC SHRINKFILE ([DBName]_Log, 500);
-- 3. 恢復完整模式並立即完整備份
ALTER DATABASE [DBName] SET RECOVERY FULL;
BACKUP DATABASE [DBName] TO DISK = '...' WITH INIT, COMPRESSION, CHECKSUM;
資料遷移：備份還原 vs 匯入匯出
| 場景 | 推薦方式 | 理由 |
|---|
| 同版本/相容版本、完整遷移 | 備份 + 還原 (WITH MOVE) | 保留所有物件、權限、統計、Broker、全文索引 |
| 跨版本升級、僅遷移資料 | SSIS / BCP / 匯入匯出精靈 | 可轉換型別、篩選資料、重建索引統計 |
| 異構遷移 (Oracle/MySQL → SQL Server) | SSMA (SQL Server Migration Assistant) | 自動轉換結構、程式碼、資料驗證 |
關鍵監控與告警指標
- 備份作業失敗 / 耗時異常增長。
- 備份檔案大小突變 (可能資料異常增長或壓縮失效)。
- 記錄檔使用率 > 80% (長時間無記錄備份或長跑交易)。
msdb..backupset 記錄與磁碟實際檔案一致性。- 定期
RESTORE VERIFYONLY 或 CHECKSUM 驗證備份可讀性。
