SQL Server 2005：如何仅复制备份后变更的表差异数据？

这情况真的让人头大——劣质软件打着“更新”的旗号回滚了加密存储过程和用户定义函数，还刚好赶上备份后有一小时的数据变更窗口，还好你提前做了备份，不然损失更大。针对你想仅同步差异数据的需求，我整理了几个SQL Server环境下可行的技术方案：

如果你的SQL Server开启了完整恢复模式，并且在备份后到故障发现前有定期的事务日志备份，或者当前数据库的事务日志还没被截断，这是最精准的方案：

1. 先把备份恢复到一个备用数据库（别直接覆盖生产库），恢复时指定NORECOVERY以便后续追加事务日志：

   RESTORE DATABASE DB_Recovery FROM DISK = 'D:\Backups\DB_FullBackup.bak'
   WITH NORECOVERY, REPLACE, MOVE 'DB_Data' TO 'D:\Data\DB_Recovery.mdf',
   MOVE 'DB_Log' TO 'D:\Logs\DB_Recovery.ldf';
   

2. 然后依次追加备份后到故障时间点的所有事务日志备份，最后一次恢复时指定RECOVERY和STOPAT来截断到故障发现的时间：

   RESTORE LOG DB_Recovery FROM DISK = 'D:\Backups\DB_LogBackup1.trn'
   WITH NORECOVERY;
   
   -- 最后一次日志恢复，精准停到故障发现时间
   RESTORE LOG DB_Recovery FROM DISK = 'D:\Backups\DB_LogBackupLast.trn'
   WITH RECOVERY, STOPAT = '2024-05-20 11:00:00'; -- 故障发现的时间
   

3. 如果没有单独的事务日志备份，也可以直接读取当前生产库的在线事务日志，提取备份后到故障时间的所有DML操作：

   -- 使用fn_dblog读取在线日志，筛选备份时间后的INSERT/UPDATE/DELETE操作
   SELECT 
       Operation,
       Context,
       AllocUnitName,
       [Transaction ID],
       [Begin Time],
       [RowLog Contents 0]
   FROM fn_dblog(NULL, NULL)
   WHERE 
       [Begin Time] > '2024-05-20 10:00:00' -- 备份完成时间
       AND [Begin Time] < '2024-05-20 11:00:00' -- 故障发现时间
       AND Operation IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW');
   

   注意：fn_dblog的输出需要一定的解析能力，你可以根据AllocUnitName定位到具体表，然后提取对应的变更语句，手动解析虽然麻烦，但能实现无工具依赖的差异提取。

如果你的业务表有LastModifiedTime这类记录数据最后修改时间的字段，或者主键明确，那可以直接在备用恢复库和生产库之间做表级对比同步：

1. 先把备份恢复到备用库（完整恢复，不需要NORECOVERY）。
2. 对每个有数据变更的表，用MERGE语句同步差异：

   -- 假设备用库是DB_Backup，生产库是DB_Production，表有主键ID和LastModified字段
   MERGE INTO DB_Production.dbo.YourTable AS Target
   USING (
       SELECT ID, Column1, Column2, LastModified
       FROM DB_Backup.dbo.YourTable
       WHERE LastModified > '2024-05-20 10:00:00' -- 备份时间
   ) AS Source ON Target.ID = Source.ID
   WHEN MATCHED THEN
       UPDATE SET Target.Column1 = Source.Column1, Target.Column2 = Source.Column2
   WHEN NOT MATCHED BY Target THEN
       INSERT (ID, Column1, Column2, LastModified)
       VALUES (Source.ID, Source.Column1, Source.Column2, Source.LastModified)
   WHEN NOT MATCHED BY Source THEN
       DELETE; -- 注意：这一步会删除生产库中备份后新增但备用库没有的行，需谨慎验证后执行！
   

   提示：如果没有LastModified字段，可以用CHECKSUM对比整行数据来找出差异，但效率较低，适合小表场景。

如果之前没开CDC，现在临时开启也能捕获后续的变更，但对于已经发生的一小时变更，这个方案主要用于辅助验证：

1. 对生产库开启CDC：

   USE DB_Production;
   EXEC sys.sp_cdc_enable_db;
   EXEC sys.sp_cdc_enable_table 
       @source_schema = N'dbo',
       @source_name = N'YourTable',
       @role_name = NULL;
   

2. 开启后可以通过CDC的系统表查看后续的变更记录，配合前面的方案验证同步是否完整，避免遗漏。

• 所有操作先在测试环境验证，绝对不要直接操作生产库！
• 恢复加密存储过程和UDF后，要检查权限是否和原来一致，避免关联软件出现权限报错。
• 如果事务日志已经被截断（比如使用简单恢复模式），那方案1就不可用，只能依赖方案2或者第三方日志解析工具。

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