如何在SQL Server中仅保留指定数量的数据库备份并自动清理旧备份

针对你需要定时保留2-3份数据库备份、自动删除旧文件的需求，提供两种可行的本地实现方案：

方案一：SQL Server代理作业 + T-SQL脚本 #

适合已启用SQL Server代理的环境，直接在数据库层面完成清理：

步骤1：编写清理脚本 #

-- 配置参数
DECLARE @BackupRootPath NVARCHAR(500) = N'C:\SQL_Backups\'; -- 替换为你的备份根目录
DECLARE @RetainBackupCount INT = 3; -- 每个数据库保留的备份份数

-- 生成并执行删除命令（先验证再执行）
WITH RankedBackups AS (
    SELECT 
        bmf.physical_device_name,
        bs.database_name,
        ROW_NUMBER() OVER (
            PARTITION BY bs.database_name 
            ORDER BY bs.backup_finish_date DESC
        ) AS BackupOrder
    FROM msdb.dbo.backupset bs
    JOIN msdb.dbo.backupmediafamily bmf 
        ON bs.media_set_id = bmf.media_set_id
    WHERE 
        bs.type = 'D' -- 仅处理完整备份，差异备份用'I'，日志备份用'L'
        AND bmf.physical_device_name LIKE @BackupRootPath + '%'
)
-- 先执行SELECT查看待删除文件，确认无误后替换为EXEC xp_cmdshell
SELECT 
    'DEL "' + physical_device_name + '"' AS DeleteCommand
FROM RankedBackups
WHERE BackupOrder > @RetainBackupCount;

• 先执行SELECT语句预览要删除的文件路径，确认无问题后，再将SELECT替换为EXEC xp_cmdshell执行删除（需确保SQL Server账户有目录删除权限，且xp_cmdshell已启用）
• 若需区分dev和prod库，可在WHERE子句中添加AND bs.database_name LIKE 'Dev_%'或AND bs.database_name LIKE 'Prod_%'过滤

步骤2：创建定时作业 #

1. 打开SQL Server代理，新建作业
2. 作业步骤中选择执行上述T-SQL脚本
3. 计划设置为每周两次（与你的备份任务时间错开或同步）

方案二：PowerShell脚本 + Windows任务计划 #

适合不想启用xp_cmdshell的场景，灵活性更高：

步骤1：编写清理脚本 #

# 配置参数
$backupDirs = @(
    "D:\Backups\Production\",
    "D:\Backups\Development\"
)
$keepCount = 3

# 遍历备份目录，按数据库分组清理旧文件
foreach ($dir in $backupDirs) {
    Get-ChildItem -Path $dir -Filter "*.bak" |
        # 按数据库名分组（假设备份文件名格式为[DBName]_Full_20240520.bak，根据实际格式调整）
        Group-Object { $_.Name.Split('_')[0] } |
        ForEach-Object {
            # 保留最新N份，删除其余
            $_.Group | Sort-Object LastWriteTime -Descending |
                Select-Object -Skip $keepCount |
                Remove-Item -Force -WhatIf # 移除-WhatIf实际执行删除
        }
}

• 调整$backupDirs为你的dev/prod备份目录
• 修改分组规则（Split('_')[0]）匹配你的备份文件名格式
• 先保留-WhatIf参数运行脚本，预览要删除的文件，确认后移除该参数

步骤2：创建定时任务 #

1. 打开Windows任务计划程序，创建基本任务
2. 设置触发时间为每周两次
3. 操作选择“启动程序”，程序选择powershell.exe，参数为你的脚本路径（如-File "C:\Scripts\CleanupBackups.ps1"）
4. 确保执行任务的账户有备份目录的删除权限

关键注意事项 #

• 权限验证：执行脚本的账户（SQL Server代理账户或Windows任务账户）必须拥有备份目录的修改/删除权限
• 备份校验：建议在清理前验证备份文件的完整性，可在脚本中加入校验逻辑（如SQL Server的RESTORE VERIFYONLY）
• 环境隔离：dev和prod的备份目录尽量物理分离，避免误操作

内容的提问来源于stack exchange，提问作者Miaozxje