如何高效实现SQL Server特定带WHERE条件表的每小时.SQB备份

针对你提出的SQL Server特定表每小时带过滤的SQB备份需求，结合源表结构动态变化的场景，我整理了一套基于临时数据库中转的可行方案——毕竟直接对源表做带过滤的备份不太好适配结构变更，用临时库中转既能同步最新结构，又能灵活过滤数据，完全符合你允许目标端删/截断的要求。下面是具体的实现步骤和细节：

核心逻辑是：每次备份前，先把源表的最新结构同步到临时库（同步前先清空临时库对应表），再将源表中符合过滤条件的数据导入临时表，最后对临时库（或单独的表对象）生成SQB格式的备份文件，通过SQL Server Agent实现每小时自动调度。

因为源表结构会随时变化（增删列、索引等），所以必须每次备份前都同步最新结构，而不是一次性创建临时表。这里用动态SQL查询系统视图来生成结构创建语句，示例如下：

-- 配置参数
DECLARE @SourceDB NVARCHAR(128) = 'YourSourceDatabaseName';
DECLARE @TempDB NVARCHAR(128) = 'YourTempDatabaseName';
DECLARE @TargetTableName NVARCHAR(128) = 'YourSpecificTableName';

-- 1. 处理临时表：存在则截断，不存在则创建
DECLARE @CreateTableSQL NVARCHAR(MAX);
SELECT @CreateTableSQL = 
    'USE ' + QUOTENAME(@TempDB) + '; ' +
    'IF OBJECT_ID(''' + QUOTENAME(@TargetTableName) + ''') IS NOT NULL ' +
        'TRUNCATE TABLE ' + QUOTENAME(@TargetTableName) + '; ' +
    'ELSE ' +
        'CREATE TABLE ' + QUOTENAME(@TargetTableName) + ' (' +
        STRING_AGG(
            QUOTENAME(c.name) + ' ' + t.name +
            -- 处理带长度/精度的数据类型
            CASE 
                WHEN t.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN 
                    '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS NVARCHAR) END + ')'
                WHEN t.name IN ('decimal', 'numeric') THEN 
                    '(' + CAST(c.precision AS NVARCHAR) + ',' + CAST(c.scale AS NVARCHAR) + ')'
                ELSE '' 
            END +
            -- 处理非空约束
            CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END,
            ', '
        ) + ');'
FROM @SourceDB.sys.columns c
JOIN @SourceDB.sys.types t 
    ON c.system_type_id = t.system_type_id 
    AND c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(QUOTENAME(@SourceDB) + '.' + QUOTENAME(@TargetTableName));

EXEC sp_executesql @CreateTableSQL;

-- 2. 同步索引（可扩展到外键、触发器等对象）
DECLARE @CreateIndexSQL NVARCHAR(MAX);
SELECT @CreateIndexSQL = STRING_AGG(
    'USE ' + QUOTENAME(@TempDB) + '; ' +
    'CREATE ' +
    CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
    CASE WHEN i.index_id = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
    'INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(@TargetTableName) + ' (' +
    STRING_AGG(
        QUOTENAME(c.name) + ' ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END,
        ', '
    ) + ');',
    ' '
)
FROM @SourceDB.sys.indexes i
JOIN @SourceDB.sys.index_columns ic 
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id
JOIN @SourceDB.sys.columns c 
    ON ic.object_id = c.object_id 
    AND ic.column_id = c.column_id
WHERE 
    i.object_id = OBJECT_ID(QUOTENAME(@SourceDB) + '.' + QUOTENAME(@TargetTableName))
    AND i.type > 0; -- 排除堆表的默认索引

IF @CreateIndexSQL IS NOT NULL
    EXEC sp_executesql @CreateIndexSQL;

结构同步完成后，用INSERT INTO...SELECT语句将符合WHERE条件的数据导入临时表，示例如下：

DECLARE @SourceDB NVARCHAR(128) = 'YourSourceDatabaseName';
DECLARE @TempDB NVARCHAR(128) = 'YourTempDatabaseName';
DECLARE @TargetTableName NVARCHAR(128) = 'YourSpecificTableName';
-- 这里替换为你针对该表的过滤条件，比如按小时过滤
DECLARE @FilterClause NVARCHAR(MAX) = 'WHERE CreatedTime >= DATEADD(HOUR, -1, GETDATE())';

DECLARE @InsertSQL NVARCHAR(MAX) = 
    'USE ' + QUOTENAME(@TempDB) + '; ' +
    'INSERT INTO ' + QUOTENAME(@TargetTableName) + ' ' +
    'SELECT * FROM ' + QUOTENAME(@SourceDB) + '.' + QUOTENAME(@TargetTableName) + ' ' +
    @FilterClause + ';';

EXEC sp_executesql @InsertSQL;

SQB是Redgate SQL Backup的专属格式，需要使用其提供的sqlbackup存储过程来生成备份。如果你需要每张表单独备份，可以考虑将每张表放在临时库的单独文件组中；如果接受备份整个临时库（因为临时库只存放当前小时的过滤数据），可以用以下脚本：

DECLARE @BackupRootPath NVARCHAR(256) = 'D:\SQLBackups\Hourly\';
DECLARE @TableName NVARCHAR(128) = 'YourSpecificTableName';
-- 生成带时间戳的备份文件名，避免重复
DECLARE @Timestamp NVARCHAR(32) = REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), ' ', '_'), ':', '-');
DECLARE @BackupFilePath NVARCHAR(256) = @BackupRootPath + @TableName + '_hourly_backup_' + @Timestamp + '.sqb';

-- 调用SQL Backup存储过程执行备份
EXEC master.dbo.sqlbackup 
    '-SQL "BACKUP DATABASE [' + @TempDB + '] TO DISK = ''' + @BackupFilePath + ''' WITH COMPRESSION, INIT, CHECKSUM"';

用SQL Server Agent创建一个作业，将上述步骤整合成作业步骤，然后设置调度为每小时执行一次：

• 作业步骤1：执行结构同步脚本（如果有多个表，需要循环遍历所有目标表，可通过配置表存储需要备份的表列表）
• 作业步骤2：执行数据导入脚本
• 作业步骤3：执行SQB备份脚本
• 可选步骤4：备份完成后清空临时库数据，释放存储空间

• 结构同步完整性：上述示例仅覆盖了表结构和索引，若源表有外键、触发器、默认值等对象，需要扩展动态SQL来同步这些内容
• 过滤条件灵活性：建议把每个表的过滤条件存储在一个专门的配置表（比如dbo.BackupTableFilters）中，包含TableName、FilterClause字段，动态读取后执行，避免硬编码
• 备份验证：可以在备份步骤后添加验证逻辑，使用sqlbackup的-VERIFY参数检查备份文件的可用性
• 资源占用：虽然你说不考虑效率，但还是建议在业务低峰期调整调度，或者限制同步数据的量，避免影响源库性能

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