如何删除SQL Server历史数据库快照并保留指定数量最新快照

前置依赖 #

• 流水线运行代理需安装PowerShell的SqlServer模块，可提前执行Install-Module -Name SqlServer -Force -AllowClobber完成安装
• 流水线所用的SQL身份验证账号需具备删除数据库的权限

场景1：删除所有历史快照后创建新快照 #

和你提供的原生SQL逻辑完全对齐，支持流水线变量传参：

# 可配置参数，支持从流水线变量传入
$sqlInstance = "你的SQL实例地址"
$dbMatchRule = "%Dev%"  # 快照名称匹配规则，和你原有SQL逻辑一致
$sqlUser = "$(SQL_USER)"  # 建议用流水线保密变量存储，避免硬编码
$sqlPwd = "$(SQL_PWD)"

# 构造批量删除SQL
$dropAllSql = @"
DECLARE @Sql as NVARCHAR(MAX) = (SELECT 'DROP DATABASE ['+ name + ']; ' FROM sys.databases WHERE name like '$dbMatchRule' FOR XML PATH('')) 
EXEC sys.sp_executesql @Sql
"@

# 执行删除操作
Invoke-SqlCmd -ServerInstance $sqlInstance -Username $sqlUser -Password $sqlPwd -Query $dropAllSql -ErrorAction Stop

# 此处追加你原有创建新快照的逻辑即可
# 示例：调用创建快照的SQL
# $createSnapshotSql = "你的创建快照SQL语句"
# Invoke-SqlCmd -ServerInstance $sqlInstance -Username $sqlUser -Password $sqlPwd -Query $createSnapshotSql -ErrorAction Stop

场景2：删除旧快照仅保留最近2-3个最新快照 #

按快照创建时间倒序筛选，跳过指定数量的最新快照后删除剩余历史版本：

# 可配置参数，支持从流水线变量传入
$sqlInstance = "你的SQL实例地址"
$dbMatchRule = "%Dev%"  # 快照名称匹配规则
$keepSnapshotCount = 3  # 保留的最新快照数量，可按需改为2
$sqlUser = "$(SQL_USER)"
$sqlPwd = "$(SQL_PWD)"

# 构造按时间筛选的批量删除SQL
$dropOldSql = @"
DECLARE @Sql as NVARCHAR(MAX) = (
    SELECT 'DROP DATABASE ['+ name + ']; ' 
    FROM sys.databases 
    WHERE name like '$dbMatchRule'
    ORDER BY create_date DESC
    OFFSET $keepSnapshotCount ROWS FETCH NEXT 1000 ROWS ONLY
    FOR XML PATH('')
) 
IF @Sql IS NOT NULL EXEC sys.sp_executesql @Sql
"@

# 执行删除操作
Invoke-SqlCmd -ServerInstance $sqlInstance -Username $sqlUser -Password $sqlPwd -Query $dropOldSql -ErrorAction Stop

流水线集成注意事项 #

• 所有敏感信息（SQL账号、密码、实例地址）建议使用流水线内置的保密变量存储，不要明文写在脚本中
• 可在执行删除前加日志打印步骤，输出待删除的快照名称，避免匹配规则错误导致误删正常数据库
• 如果使用Windows身份验证连接SQL Server，去掉-Username和-Password参数即可
• 若无法安装SqlServer模块，可替换为调用系统内置的sqlcmd命令执行SQL，示例：sqlcmd -S $sqlInstance -U $sqlUser -P $sqlPwd -Q $dropAllSql

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