SQL Server 2000 自動化備份作業腳本：全量/差異/日誌三層策略

備份策略設計

恢復模式必須為 FULL：ALTER DATABASE Test SET RECOVERY FULL;

SQL Server Agent 作業腳本 (可直接在查詢分析器執行創建)

-- ========== 1. 全量備份作業 ==========
EXEC msdb.dbo.sp_add_job
    @job_name = N'DB_Backup_Full',
    @enabled = 1,
    @description = N'每週日 01:00 全量備份雙副本',
    @category_name = N'Database Maintenance';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DB_Backup_Full',
    @step_name = N'Full_A',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [Test] TO DISK = N''E:\Backup\test_full_A.bak'' WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;',
    @on_success_action = 3, @on_fail_action = 3,
    @retry_attempts = 3, @retry_interval = 5;
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DB_Backup_Full',
    @step_name = N'Full_B',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [Test] TO DISK = N''E:\Backup\test_full_B.bak'' WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;',
    @retry_attempts = 3, @retry_interval = 5;
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'DB_Backup_Full',
    @name = N'Weekly_Sunday_0100',
    @freq_type = 8,           -- Weekly
    @freq_interval = 1,       -- Sunday
    @freq_recurrence_factor = 1,
    @active_start_time = 10000; -- 01:00
GO

-- ========== 2. 差異備份作業 ==========
EXEC msdb.dbo.sp_add_job
    @job_name = N'DB_Backup_Diff',
    @enabled = 1,
    @description = N'每日 01:00 差異備份雙副本 (追加)',
    @category_name = N'Database Maintenance';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DB_Backup_Diff',
    @step_name = N'Diff_A',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [Test] TO DISK = N''E:\Backup\test_df_A.bak'' WITH DIFFERENTIAL, NOINIT, CHECKSUM, STATS = 10;',
    @retry_attempts = 3, @retry_interval = 5;
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DB_Backup_Diff',
    @step_name = N'Diff_B',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [Test] TO DISK = N''E:\Backup\test_df_B.bak'' WITH DIFFERENTIAL, NOINIT, CHECKSUM, STATS = 10;',
    @retry_attempts = 3, @retry_interval = 5;
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'DB_Backup_Diff',
    @name = N'Daily_Except_Sunday_0100',
    @freq_type = 4,           -- Daily
    @freq_interval = 1,
    @freq_recurrence_factor = 1,
    @active_start_time = 10000;
GO
-- 注意：需手工刪除週日排程或在步驟首行加判斷跳過

-- ========== 3. 事務日誌備份作業 ==========
EXEC msdb.dbo.sp_add_job
    @job_name = N'DB_Backup_Log',
    @enabled = 1,
    @description = N'每小時事務日誌備份雙副本 (追加)',
    @category_name = N'Database Maintenance';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DB_Backup_Log',
    @step_name = N'Log_A',
    @subsystem = N'TSQL',
    @command = N'BACKUP LOG [Test] TO DISK = N''E:\Backup\test_log_A.bak'' WITH NOINIT, CHECKSUM, STATS = 10;',
    @retry_attempts = 3, @retry_interval = 5;
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DB_Backup_Log',
    @step_name = N'Log_B',
    @subsystem = N'TSQL',
    @command = N'BACKUP LOG [Test] TO DISK = N''E:\Backup\test_log_B.bak'' WITH NOINIT, CHECKSUM, STATS = 10;',
    @retry_attempts = 3, @retry_interval = 5;
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'DB_Backup_Log',
    @name = N'Hourly_Every_60min',
    @freq_type = 4,           -- Daily
    @freq_interval = 1,
    @freq_subday_type = 8,    -- Hours
    @freq_subday_interval = 1,
    @active_start_time = 0,
    @active_end_time = 235959;
GO

關鍵參數解析

• INIT：覆蓋文件 (全量備份雙副本各自獨立文件)。
• NOINIT：追加到文件尾 (差異/日誌雙副本各自形成備份集鏈)。
• COMPRESSION：SQL 2008+ 支持，2000 需第三方工具 (如 LiteSpeed/Red Gate) 或腳本壓縮。
• CHECKSUM：啟用頁面校驗和，防止寫入損壞頁。
• STATS = 10：每 10% 進度向錯誤日誌/作業歷史輸出進度，便於監控。

監控與驗證體系

1. 作業失敗告警：各作業屬性 → 通知 → 寫入應用程序事件日誌 / 發送郵件 (需配置 Database Mail / SQLMail)。
2. 備份有效性驗證：每週額外創建作業，RESTORE VERIFYONLY FROM DISK = '...' 抽樣驗證。
3. 磁盤空間預警：Agent 警報 → 性能條件 → `SQLServer:Databases` / `Log File(s) Used Size (KB)` 閾值觸發。
4. 備份鏈完整性檢查：RESTORE HEADERONLY 解析備份集 LSN 鏈，確保無斷點。

現代化替代方案 (SQL Server 2005+)

• 維護計劃：圖形化向導生成 SSIS 包，支持子計劃、清理任務、報告郵件。
• Ola Hallengren 脚本：社區標準化備份/完整性/索引維護腳本，參數豐富、跨版本兼容、官網。
• Azure Blob / S3 直備：`BACKUP TO URL` (2012 SP1 CU2+) 直接寫入對象存儲，免磁盤中轉。