每日重建的Database A自动同步表结构及数据至Database B的免费替代方案

方案一：动态SQL+SQL Agent Job（推荐，完全用SQL Server原生功能） #

利用SQL Server自带的系统视图生成同步脚本，通过SQL Agent定时执行，完全匹配「覆盖B中对应表、保留B独有对象」的需求逻辑。

实现步骤： #

1. 创建同步存储过程：在数据库B中创建存储过程，自动生成并执行表结构同步、数据填充的动态SQL

USE [DatabaseB]
GO

CREATE PROCEDURE [dbo].[SyncFromDatabaseA]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SchemaName NVARCHAR(128), @TableName NVARCHAR(128), @SQL NVARCHAR(MAX);

    -- 游标遍历DatabaseA的所有表（含架构）
    DECLARE TableCursor CURSOR FOR
    SELECT s.name, t.name
    FROM DatabaseA.sys.schemas s
    JOIN DatabaseA.sys.tables t ON s.schema_id = t.schema_id
    ORDER BY s.name, t.name;

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 1. 删除DatabaseB中已存在的对应表
        SET @SQL = 'IF EXISTS(SELECT 1 FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = ''' + @SchemaName + ''' AND t.name = ''' + @TableName + ''')
                       DROP TABLE [' + @SchemaName + '].[' + @TableName + '];';
        EXEC sp_executesql @SQL;

        -- 2. 从DatabaseA生成CREATE TABLE语句并执行
        SET @SQL = 'DECLARE @CreateSQL NVARCHAR(MAX);
                   SELECT @CreateSQL = ''CREATE TABLE [' + @SchemaName + '].[' + @TableName + '] ('' + STRING_AGG(COLUMN_DEFINITION, '', '') + '')''
                   FROM (
                       SELECT ''['' + c.name + ''] '' + 
                              tp.name + 
                              CASE WHEN tp.name IN (''varchar'', ''nvarchar'', ''char'', ''nchar'') THEN ''('' + CASE WHEN c.max_length = -1 THEN ''MAX'' ELSE CAST(c.max_length AS NVARCHAR(10)) END + '')''
                                   WHEN tp.name IN (''decimal'', ''numeric'') THEN ''('' + CAST(c.precision AS NVARCHAR(10)) + '', '' + CAST(c.scale AS NVARCHAR(10)) + '')''
                                   ELSE '''' END +
                              CASE WHEN c.is_nullable = 1 THEN '' NULL'' ELSE '' NOT NULL'' END AS COLUMN_DEFINITION
                       FROM DatabaseA.sys.columns c
                       JOIN DatabaseA.sys.types tp ON c.system_type_id = tp.system_type_id AND c.user_type_id = tp.user_type_id
                       WHERE c.object_id = OBJECT_ID(''DatabaseA.[' + @SchemaName + '].[' + @TableName + ']'')
                       ORDER BY c.column_id
                   ) AS Columns;
                   EXEC sp_executesql @CreateSQL;';
        EXEC sp_executesql @SQL;

        -- 3. 从DatabaseA导入数据到DatabaseB，处理IDENTITY列
        SET @SQL = 'IF EXISTS(SELECT 1 FROM DatabaseA.sys.columns c WHERE c.object_id = OBJECT_ID(''DatabaseA.[' + @SchemaName + '].[' + @TableName + ']'') AND c.is_identity = 1)
                       SET IDENTITY_INSERT [' + @SchemaName + '].[' + @TableName + '] ON;';
        EXEC sp_executesql @SQL;

        SET @SQL = 'INSERT INTO [' + @SchemaName + '].[' + @TableName + ']
                   SELECT * FROM DatabaseA.[' + @SchemaName + '].[' + @TableName + '];';
        EXEC sp_executesql @SQL;

        SET @SQL = 'IF EXISTS(SELECT 1 FROM DatabaseA.sys.columns c WHERE c.object_id = OBJECT_ID(''DatabaseA.[' + @SchemaName + '].[' + @TableName + ']'') AND c.is_identity = 1)
                       SET IDENTITY_INSERT [' + @SchemaName + '].[' + @TableName + '] OFF;';
        EXEC sp_executesql @SQL;

        FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
    END;

    CLOSE TableCursor;
    DEALLOCATE TableCursor;
END
GO

2. 创建SQL Agent定时任务：
   • 打开SQL Server Management Studio，找到「SQL Server Agent」→「作业」→右键新建作业
   • 作业步骤选择「Transact-SQL脚本(T-SQL)」，数据库选DatabaseB，执行语句：EXEC [dbo].[SyncFromDatabaseA];
   • 调度设置为每日早晨指定时间执行

优势： #

• 完全依赖SQL Server原生功能，无额外成本
• 自动适配DatabaseA的表结构变更，无需手动修改
• 逻辑和原BIML生成的SSIS包一致：覆盖B中对应表，保留B独有对象

方案二：bcp命令行+SQL Agent Job（适合大数据量场景） #

用SQL Server自带的bcp工具导出DatabaseA的表到本地文件，再导入到DatabaseB，性能优于纯T-SQL同步。

实现步骤： #

1. 生成批处理脚本：用动态SQL生成包含bcp导出、导入命令的批处理文件

USE [DatabaseB]
GO

DECLARE @SchemaName NVARCHAR(128), @TableName NVARCHAR(128), @BatchContent NVARCHAR(MAX) = '';
DECLARE @ServerName NVARCHAR(128) = @@SERVERNAME;

-- 确保同步目录存在
EXEC xp_cmdshell 'mkdir C:\Temp\Sync', NO_OUTPUT;

DECLARE TableCursor CURSOR FOR
SELECT s.name, t.name
FROM DatabaseA.sys.schemas s
JOIN DatabaseA.sys.tables t ON s.schema_id = t.schema_id;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 导出DatabaseA的表数据
    SET @BatchContent += 'bcp "SELECT * FROM DatabaseA.' + @SchemaName + '.' + @TableName + '" queryout "C:\Temp\Sync\' + @SchemaName + '_' + @TableName + '.dat" -S ' + @ServerName + ' -T -n' + CHAR(13) + CHAR(10);
    
    -- 删除DatabaseB中对应表
    SET @BatchContent += 'sqlcmd -S ' + @ServerName + ' -d DatabaseB -Q "IF EXISTS(SELECT 1 FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name=''' + @SchemaName + ''' AND t.name=''' + @TableName + ''') DROP TABLE [' + @SchemaName + '].[' + @TableName + '];" -T' + CHAR(13) + CHAR(10);
    
    -- 生成CREATE TABLE脚本并执行
    SET @BatchContent += 'sqlcmd -S ' + @ServerName + ' -d DatabaseB -Q "DECLARE @CreateSQL NVARCHAR(MAX); SELECT @CreateSQL = ''CREATE TABLE [' + @SchemaName + '].[' + @TableName + '] ('' + STRING_AGG(COLUMN_DEFINITION, '', '') + '')'' FROM (SELECT ''['' + c.name + ''] '' + tp.name + CASE WHEN tp.name IN (''varchar'', ''nvarchar'') THEN ''('' + CASE WHEN c.max_length=-1 THEN ''MAX'' ELSE CAST(c.max_length AS NVARCHAR) END + '')'' ELSE '''' END + CASE WHEN c.is_nullable=1 THEN '' NULL'' ELSE '' NOT NULL'' END AS COLUMN_DEFINITION FROM DatabaseA.sys.columns c JOIN DatabaseA.sys.types tp ON c.system_type_id=tp.system_type_id WHERE c.object_id=OBJECT_ID(''DatabaseA.' + @SchemaName + '.' + @TableName + ''') ORDER BY c.column_id) AS Columns; EXEC sp_executesql @CreateSQL;" -T' + CHAR(13) + CHAR(10);
    
    -- 导入数据到DatabaseB
    SET @BatchContent += 'bcp DatabaseB.' + @SchemaName + '.' + @TableName + ' in "C:\Temp\Sync\' + @SchemaName + '_' + @TableName + '.dat" -S ' + @ServerName + ' -T -n' + CHAR(13) + CHAR(10);
    
    FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- 写入批处理文件
DECLARE @Cmd NVARCHAR(MAX) = 'echo ' + REPLACE(@BatchContent, CHAR(13) + CHAR(10), '^&echo ') + ' > C:\Temp\Sync\SyncAtoB.bat';
EXEC xp_cmdshell @Cmd, NO_OUTPUT;

2. 创建SQL Agent任务执行批处理：
   • 新建作业步骤，选择「操作系统(CmdExec)」，命令为：C:\Temp\Sync\SyncAtoB.bat
   • 调度设置为每日早晨执行

优势： #

• 大数据量下同步速度更快
• 自动适配表结构变更，无需手动干预

注意事项 #

• 确保SQL Agent服务已启动
• 方案二中需赋予SQL Server服务账号对C:\Temp\Sync目录的读写权限
• 若需要同步视图、存储过程等对象，可扩展脚本从sys.views、sys.procedures等系统视图生成对应同步逻辑

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