如何在T-SQL中限制需保留的表备份数量？

针对你提出的备份保留规则，我们可以通过CTE（公共表表达式）+ 窗口函数的组合精准筛选需要保留的备份表，同时兼容备份缺失的场景。以下是具体实现思路和脚本：

核心思路 #

1. 收集所有备份表的元数据（备份时间、表名等）；
2. 按规则分批次标记需要保留的备份：
   • 最近14天的全部备份；
   • 21天前、28天前的最近一份备份（即使当天无备份，自动取最近的更早备份）；
   • 超过28天后，每月保留最后一份备份；
   • 超过1年后，每年保留最后一份备份；
3. 生成删除脚本或直接删除未标记的备份表。

具体脚本 #

场景1：有专门的备份历史表（推荐） #

假设你有一张BackupHistory表，记录所有备份表的信息：

CREATE TABLE BackupHistory (
    BackupID INT IDENTITY(1,1) PRIMARY KEY,
    BackupTableName NVARCHAR(128) NOT NULL,
    BackupDateTime DATETIME NOT NULL -- 备份创建的准确时间
);

使用以下脚本筛选并删除无需保留的备份：

WITH AllBackups AS (
    SELECT 
        BackupTableName,
        BackupDateTime,
        CAST(BackupDateTime AS DATE) AS BackupDate,
        -- 提取月份维度，用于每月备份筛选
        DATEFROMPARTS(YEAR(BackupDateTime), MONTH(BackupDateTime), 1) AS BackupMonth,
        -- 提取年份维度，用于每年备份筛选
        DATEFROMPARTS(YEAR(BackupDateTime), 1, 1) AS BackupYear,
        DATEDIFF(DAY, BackupDateTime, GETDATE()) AS DaysOld
    FROM BackupHistory
),
KeepBackups AS (
    -- 1. 保留最近14天的所有备份
    SELECT BackupTableName
    FROM AllBackups
    WHERE DaysOld <= 14

    UNION

    -- 2. 保留21天前的最近一份备份（无当日备份则取最近更早的）
    SELECT TOP 1 BackupTableName
    FROM AllBackups
    WHERE BackupDate <= DATEADD(DAY, -21, GETDATE())
    ORDER BY BackupDateTime DESC

    UNION

    -- 3. 保留28天前的最近一份备份（逻辑同上）
    SELECT TOP 1 BackupTableName
    FROM AllBackups
    WHERE BackupDate <= DATEADD(DAY, -28, GETDATE())
    ORDER BY BackupDateTime DESC

    UNION

    -- 4. 超过28天的备份，每月保留最后一份
    SELECT BackupTableName
    FROM (
        SELECT 
            BackupTableName,
            ROW_NUMBER() OVER (PARTITION BY BackupMonth ORDER BY BackupDateTime DESC) AS RowNum
        FROM AllBackups
        WHERE DaysOld > 28
    ) MonthlyBackups
    WHERE RowNum = 1

    UNION

    -- 5. 超过1年的备份，每年保留最后一份
    SELECT BackupTableName
    FROM (
        SELECT 
            BackupTableName,
            ROW_NUMBER() OVER (PARTITION BY BackupYear ORDER BY BackupDateTime DESC) AS RowNum
        FROM AllBackups
        WHERE DaysOld > 365
    ) YearlyBackups
    WHERE RowNum = 1
)
-- 第一步：生成删除脚本，手动确认后再执行（强烈推荐）
SELECT 'DROP TABLE ' + QUOTENAME(BackupTableName) AS DropCommand
FROM AllBackups
WHERE BackupTableName NOT IN (SELECT BackupTableName FROM KeepBackups)

-- 第二步：确认无误后，执行以下语句删除表并更新历史记录
/*
DECLARE @DropSQL NVARCHAR(MAX) = ''
SELECT @DropSQL += 'DROP TABLE ' + QUOTENAME(BackupTableName) + ';'
FROM AllBackups
WHERE BackupTableName NOT IN (SELECT BackupTableName FROM KeepBackups)

EXEC sp_executesql @DropSQL

DELETE FROM BackupHistory
WHERE BackupTableName NOT IN (SELECT BackupTableName FROM KeepBackups)
*/

场景2：无备份历史表，从系统表解析表名 #

如果备份表命名规则包含日期（如FactSales_Backup_20240520），可以直接从sys.tables解析日期：

WITH AllBackups AS (
    SELECT 
        name AS BackupTableName,
        -- 解析表名中的8位日期
        TRY_CAST(SUBSTRING(name, CHARINDEX('_Backup_', name) + 8, 8) AS DATETIME) AS BackupDateTime,
        TRY_CAST(SUBSTRING(name, CHARINDEX('_Backup_', name) + 8, 8) AS DATE) AS BackupDate,
        DATEFROMPARTS(YEAR(TRY_CAST(SUBSTRING(name, CHARINDEX('_Backup_', name) + 8, 8) AS DATE)), MONTH(TRY_CAST(SUBSTRING(name, CHARINDEX('_Backup_', name) + 8, 8) AS DATE)), 1) AS BackupMonth,
        DATEFROMPARTS(YEAR(TRY_CAST(SUBSTRING(name, CHARINDEX('_Backup_', name) + 8, 8) AS DATE)), 1, 1) AS BackupYear,
        DATEDIFF(DAY, TRY_CAST(SUBSTRING(name, CHARINDEX('_Backup_', name) + 8, 8) AS DATE), GETDATE()) AS DaysOld
    FROM sys.tables
    -- 根据你的备份表命名规则调整筛选条件
    WHERE name LIKE '%_Backup_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
),
KeepBackups AS (
    -- 同场景1的KeepBackups逻辑
    SELECT BackupTableName FROM AllBackups WHERE DaysOld <=14
    UNION
    SELECT TOP 1 BackupTableName FROM AllBackups WHERE BackupDate <= DATEADD(DAY,-21,GETDATE()) ORDER BY BackupDateTime DESC
    UNION
    SELECT TOP 1 BackupTableName FROM AllBackups WHERE BackupDate <= DATEADD(DAY,-28,GETDATE()) ORDER BY BackupDateTime DESC
    UNION
    SELECT BackupTableName FROM (SELECT BackupTableName,ROW_NUMBER() OVER(PARTITION BY BackupMonth ORDER BY BackupDateTime DESC) AS RowNum FROM AllBackups WHERE DaysOld>28) t WHERE RowNum=1
    UNION
    SELECT BackupTableName FROM (SELECT BackupTableName,ROW_NUMBER() OVER(PARTITION BY BackupYear ORDER BY BackupDateTime DESC) AS RowNum FROM AllBackups WHERE DaysOld>365) t WHERE RowNum=1
)
-- 生成删除脚本
SELECT 'DROP TABLE ' + QUOTENAME(BackupTableName) AS DropCommand
FROM AllBackups
WHERE BackupTableName NOT IN (SELECT BackupTableName FROM KeepBackups)

关键注意事项 #

• 安全验证：务必先执行生成删除脚本的语句，确认要删除的表符合预期后，再执行实际删除操作，避免误删重要数据。
• 备份缺失兼容：对于21天/28天前无备份的场景，通过TOP 1 + ORDER BY BackupDateTime DESC自动取最近的更早备份，符合需求。
• 性能优化：如果备份表数量庞大，建议在BackupHistory表的BackupDateTime字段建立索引，或在sys.tables的name字段建立筛选索引。
• 去重处理：使用UNION而非UNION ALL，自动去重同时满足多个保留规则的备份表（如年末的最后一份备份同时属于当月和当年保留规则）。

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