SQL Server 自動備份腳本：定時備份並自動清理 N 天前舊備份文件

核心需求

• 每日自動全量備份數據庫至指定目錄。
• 文件名包含日期，便於識別。
• 自動刪除超過保留天數 (如 5 天) 的舊備份文件，釋放磁盤空間。
• 作為 SQL Server Agent 作業步驟部署，實現無人值守運行。

完整 T-SQL 腳本

DECLARE @filename VARCHAR(255),
        @date DATETIME,
        @olddate DATETIME;

-- 1. 構建帶日期的備份文件路徑
SELECT @date = GETDATE();
SELECT @filename = 'G:\Backup\MyDatabase-' 
    + CAST(DATEPART(yyyy, @date) AS VARCHAR) + '-'
    + RIGHT('0' + CAST(DATEPART(mm, @date) AS VARCHAR), 2) + '-'
    + RIGHT('0' + CAST(DATEPART(dd, @date) AS VARCHAR), 2) 
    + '.bak';

-- 2. 執行全量備份 (WITH INIT 覆蓋同名文件)
BACKUP DATABASE [MyDatabase] TO DISK = @filename WITH INIT, COMPRESSION, STATS = 10;
GO

-- 3. 計算截止日期 (保留 5 天)
SELECT @olddate = DATEADD(day, -5, GETDATE());

-- 4. 刪除舊備份文件 (需啟用 xp_cmdshell 或使用維護計劃/外部腳本)
-- 方法 A：xp_delete_file (SQL 2008+ 內置，僅限備份文件類型)
EXECUTE master.dbo.xp_delete_file 
    0,                          -- 文件類型: 0=備份文件, 1=報表文件
    N'G:\Backup',               -- 目錄路徑
    N'bak',                     -- 副名過濾
    @olddate,                   -- 截止日期
    1;                          -- 遞刪子目錄

-- 方法 B：xp_cmdshell (需啟用，通用性強)
-- EXEC master..xp_cmdshell 'forfiles /p "G:\Backup" /m *.bak /d -5 /c "cmd /c del @path"';

關鍵參數說明

部署為 SQL Server Agent 定時作業

1. 展開 SQL Server Agent → 右鍵 作業 → 新建作業。
2. 常規：命名 (如 Daily_Backup_MyDatabase)，類別選 數據庫維護。
3. 步驟 → 新建：
   • 步驟名：Backup_And_Cleanup
   • 類型：Transact-SQL 腳本 (T-SQL)
   • 數據庫：master
   • 命令：粘貼上述完整腳本 (修改數據庫名、路徑、保留天數)。
4. 計劃 → 新建：
   • 類型：定期，頻率：每天，時間：凌晨 02:00 (業務低峰期)。
5. 通知：可配置失敗時發送郵件給 DBA (需先配置 Database Mail)。
6. 保存，啟用作業。

進階增強版：支持差異/日志備份、多數據庫、郵件通知

-- 參數化版本，可作為存儲過程部署
CREATE PROCEDURE dbo.sp_AutoBackupDatabase
    @DatabaseName SYSNAME,
    @BackupPath   NVARCHAR(500) = N'G:\Backup',
    @RetainDays   INT = 5,
    @BackupType   CHAR(1) = 'F',  -- F=全量, D=差異, L=日志
    @Compress     BIT = 1,
    @NotifyEmail  NVARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @filename NVARCHAR(600), @dt DATETIME = GETDATE(), @olddt DATETIME, @subject NVARCHAR(200), @body NVARCHAR(MAX), @rc INT;

    -- 文件名: DB_類型_YYYYMMDD_HHMMSS.bak/trn
    SET @filename = @BackupPath + N'\' + @DatabaseName + N'_' + @BackupType 
        + N'_' + CONVERT(CHAR(8), @dt, 112) + N'_' + REPLACE(CONVERT(CHAR(8), @dt, 108), ':', '') 
        + CASE @BackupType WHEN 'L' THEN N'.trn' ELSE N'.bak' END;

    BEGIN TRY
        -- 執行備份
        DECLARE @cmd NVARCHAR(MAX) = N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) 
            + N' TO DISK = ' + QUOTENAME(@filename, '''')
            + CASE @BackupType 
                WHEN 'D' THEN N' WITH DIFFERENTIAL' 
                WHEN 'L' THEN N'' -- 日志備份語法不同
              END
            + CASE WHEN @Compress = 1 THEN N', COMPRESSION' ELSE N'' END
            + N', STATS = 10;';
        
        IF @BackupType = 'L'
            SET @cmd = N'BACKUP LOG ' + QUOTENAME(@DatabaseName) 
                + N' TO DISK = ' + QUOTENAME(@filename, '''') 
                + CASE WHEN @Compress = 1 THEN N', COMPRESSION' ELSE N'' END + N', STATS = 10;';

        EXEC sp_executesql @cmd;

        -- 清理舊文件
        SET @olddt = DATEADD(day, -@RetainDays, GETDATE());
        EXEC master.dbo.xp_delete_file 0, @BackupPath, 
            CASE @BackupType WHEN 'L' THEN N'trn' ELSE N'bak' END, 
            @olddt, 1;

        SET @subject = N'【成功】' + @DatabaseName + N' ' + @BackupType + N' 備份完成';
        SET @body = N'文件: ' + @filename + CHAR(13) + N'時間: ' + CONVERT(VARCHAR, GETDATE(), 120);
    END TRY
    BEGIN CATCH
        SET @subject = N'【失敗】' + @DatabaseName + N' 備份異常';
        SET @body = N'錯誤: ' + ERROR_MESSAGE() + CHAR(13) + N'時間: ' + CONVERT(VARCHAR, GETDATE(), 120);
        SET @rc = 1;
    END CATCH

    -- 發送郵件通知 (需配置 Database Mail)
    IF @NotifyEmail IS NOT NULL
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = N'DBA_Profile', 
            @recipients = @NotifyEmail, 
            @subject = @subject, 
            @body = @body;

    RETURN ISNULL(@rc, 0);
END

注意事項與最佳實踐