SQL Server Agent 作業自動化備份：存儲過程與作業腳本完整實戰

架構概覽

1. 核心備份邏輯：封裝於存儲過程 cg_DoBackupJob，支援完整/差異備份、多檔案追加/覆寫、動態檔名 (含日期時間)。
2. 排程觸發：透過 msdb 系統存儲過程建立 Agent 作業、步驟、排程、目標伺服器。

一、備份存儲過程：cg_DoBackupJob

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'cg_DoBackupJob' AND type = 'P')
    DROP PROCEDURE cg_DoBackupJob;
GO

CREATE PROCEDURE [cg_DoBackupJob]
    @DataBaseName VARCHAR(100),      -- 目標資料庫
    @FileHead VARCHAR(50),           -- 檔名前綴
    @isFullBackup BIT,               -- 1=完整, 0=差異
    @FolderPath VARCHAR(50) = 'F:\db_backup\', -- 備份目錄 (需 SQL Server 服務帳號寫入權限)
    @BackName VARCHAR(100) = 'unknown',        -- 說明字串
    @isAppendMedia BIT = 1           -- 0=覆寫(INIT), 1=追加(NOINIT)
AS
BEGIN
    DECLARE @filePath VARCHAR(150), @sql VARCHAR(1000);

    -- 組合檔名：前綴_類型_YYYYMMDD[_HHMMSS].bak
    SELECT @filePath = @FolderPath + @FileHead + '_' +
        CASE @isFullBackup WHEN 1 THEN 'FullBackup' ELSE 'DiffBackup' END + '_' +
        CONVERT(NVARCHAR(11), GETDATE(), 112) + -- YYYYMMDD
        CASE @isFullBackup WHEN 0 THEN REPLACE(CONVERT(NVARCHAR(15), GETDATE(), 114), ':', '') ELSE '' END;

    -- 動態構建 BACKUP 指令
    SELECT @sql = 'BACKUP DATABASE [' + @DataBaseName + '] TO DISK = ''' + @filePath + ''' WITH ' +
        CASE @isAppendMedia WHEN 0 THEN 'INIT' ELSE 'NOINIT' END +
        ', NOUNLOAD, ' +
        CASE @isFullBackup WHEN 0 THEN 'DIFFERENTIAL, ' ELSE '' END +
        'NAME = N''' + @BackName + '備份'', NOSKIP, STATS = 10, NOFORMAT';

    EXEC(@sql);
    -- PRINT @sql; -- 除錯用
END;
GO

-- 測試呼叫
EXECUTE cg_DoBackupJob 'cg_access911', 'access911', 1;
GO

關鍵參數說明

二、Agent 作業建立腳本 (msdb 系統存儲過程)

BEGIN TRANSACTION
DECLARE @JobID BINARY(16), @ReturnCode INT;
SELECT @ReturnCode = 0;

-- 確保分類存在
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
    EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]';

-- 刪除同名作業 (含多伺服器檢查)
DECLARE @JobID BINARY(16);
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE name = N'access911_每2周備份一次';
IF (@JobID IS NOT NULL) BEGIN
    IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE job_id = @JobID AND server_id <> 0)) BEGIN
        RAISERROR(N'已存在多伺服器同名作業，終止匯入。', 16, 1);
        GOTO QuitWithRollback;
    END
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'access911_每2周備份一次';
    SELECT @JobID = NULL;
END

-- 建立作業
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
    @job_id = @JobID OUTPUT,
    @job_name = N'access911_每2周備份一次',
    @owner_login_name = N'Access911\access911',
    @description = N'自動完整備份',
    @category_name = N'[Uncategorized (Local)]',
    @enabled = 1,
    @notify_level_eventlog = 2, -- 失敗寫入事件日誌
    @delete_level = 0;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

-- 新增作業步驟：呼叫備份 SP
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
    @job_id = @JobID, @step_id = 1, @step_name = N'每2周完整備份',
    @command = N'EXECUTE cg_DoBackupJob ''a9SupperDatabase'',''a9SupperDatabase'',1',
    @database_name = N'master', @subsystem = N'TSQL',
    @on_success_action = 1, @on_fail_action = 2; -- 成功繼續/失敗結束作業
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

-- 新增排程：每 2 週執行一次
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
    @job_id = @JobID, @name = N'每2周排程',
    @enabled = 1, @freq_type = 8, -- Weekly
    @freq_interval = 64, -- Saturday (1=Sun, 64=Sat)
    @freq_recurrence_factor = 2, -- Every 2 weeks
    @active_start_date = 20240101, @active_start_time = 020000; -- 凌晨 2 點
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

-- 指定目標伺服器
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

COMMIT TRANSACTION;
GOTO EndSave;

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
EndSave:
GO

排程參數速查 (sp_add_jobschedule)

三、部署與驗證清單

• ☐ 目標資料庫已存在且為 FULL/ BULK_LOGGED 復原模式 (差異備份需完整備份為基底)
• ☐ SQL Server Agent 服務啟動且設為自動
• ☐ 備份目錄 F:\db_backup\ 存在，SQL Server 服務帳號具備寫入權限
• ☐ 手動執行作業一次，確認 .bak 產生且 RESTORE VERIFYONLY 通過
• ☐ 設定作業失敗通知 (Database Mail → Operator → 作業屬性 → 通知)
• ☐ 定期清理過期備份檔 (維護計畫或獨立清理作業)

總結

將備份邏輯封裝為參數化存儲過程，再透過 msdb 系統存儲過程腳本化作業建立，實現「基礎設施即代碼」，利於版控、跨環境部署與災難恢復演練。