EF迁移Down脚本：如何回滚依赖历史数据的数据更新操作？

当迁移脚本需要修改包含审计列（如UpdatedBy、UpdatedDate）的数据时，直接反向更新无法恢复原始审计值，核心解决思路是在Up阶段提前备份受影响行的完整状态，以下是几种可行方案：

1. 迁移前备份到临时表（最通用可靠） #

在Up脚本中先创建临时备份表，保存要更新行的原始数据，Down脚本通过备份表反向恢复所有字段（包括审计列）。

Up脚本示例： #

-- 创建临时备份表（命名建议包含迁移日期/标识）
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Migration_Backup_UpdateColumn1_20240520')
BEGIN
    CREATE TABLE Migration_Backup_UpdateColumn1_20240520 (
        -- 替换为你的表主键及需要备份的字段
        Id INT PRIMARY KEY,
        column_1 INT,
        UpdatedBy NVARCHAR(100),
        UpdatedDate DATETIME
    )
END

-- 备份即将被更新的行
INSERT INTO Migration_Backup_UpdateColumn1_20240520 (Id, column_1, UpdatedBy, UpdatedDate)
SELECT Id, column_1, UpdatedBy, UpdatedDate
FROM your_table
WHERE column_1 = 2;

-- 执行业务更新
UPDATE your_table
SET column_1 = 1, UpdatedBy = 'SystemUser', UpdatedDate = GETDATE()
WHERE column_1 = 2;

Down脚本示例： #

-- 从备份表恢复原始数据
UPDATE t
SET t.column_1 = b.column_1,
    t.UpdatedBy = b.UpdatedBy,
    t.UpdatedDate = b.UpdatedDate
FROM your_table t
JOIN Migration_Backup_UpdateColumn1_20240520 b ON t.Id = b.Id;

-- 清理临时备份表（可选，根据团队迁移策略决定是否保留）
DROP TABLE Migration_Backup_UpdateColumn1_20240520;

2. 利用数据库变更追踪/CDC（依赖数据库特性） #

如果使用支持变更数据捕获（CDC）的数据库（如SQL Server、PostgreSQL），可以开启CDC捕获数据变更的历史镜像，Down脚本直接从CDC日志中提取原始审计值。

前置配置（仅需执行一次）： #

-- 开启数据库级CDC（SQL Server示例）
EXEC sys.sp_cdc_enable_db;

-- 开启目标表CDC
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'your_table',
    @role_name = NULL;

Down脚本示例： #

-- 从CDC日志中获取更新前的原始数据并恢复
UPDATE t
SET t.column_1 = 2,
    t.UpdatedBy = c.UpdatedBy,
    t.UpdatedDate = c.UpdatedDate
FROM your_table t
JOIN cdc.dbo_your_table_CT c ON t.Id = c.Id
WHERE c.__$operation = 3 -- 3代表更新操作的前置镜像数据
  AND t.column_1 = 1;

3. 提前规划审计历史表（长期可追溯方案） #

如果业务需要长期保留数据变更历史，可以提前创建审计历史表，每次迁移更新前将原始数据写入历史表，Down脚本从历史表中恢复最新的原始状态。

前置创建审计历史表： #

CREATE TABLE your_table_AuditHistory (
    AuditId INT IDENTITY(1,1) PRIMARY KEY,
    TableId INT, -- 关联业务表主键
    column_1 INT,
    UpdatedBy NVARCHAR(100),
    UpdatedDate DATETIME,
    ChangeType NVARCHAR(50), -- 标记变更类型，如"Migration_Update"
    ChangeInitiator NVARCHAR(100), -- 标记变更发起方，如"SystemMigration"
    ChangeDate DATETIME DEFAULT GETDATE()
)

Up脚本示例： #

-- 写入更新前的原始数据到审计历史表
INSERT INTO your_table_AuditHistory (TableId, column_1, UpdatedBy, UpdatedDate, ChangeType, ChangeInitiator)
SELECT Id, column_1, UpdatedBy, UpdatedDate, 'Migration_Update', 'SystemUser'
FROM your_table
WHERE column_1 = 2;

-- 执行业务更新
UPDATE your_table
SET column_1 = 1, UpdatedBy = 'SystemUser', UpdatedDate = GETDATE()
WHERE column_1 = 2;

Down脚本示例： #

-- 筛选出本次迁移对应的最新原始备份
WITH LatestMigrationBackup AS (
    SELECT TableId, column_1, UpdatedBy, UpdatedDate,
           ROW_NUMBER() OVER (PARTITION BY TableId ORDER BY ChangeDate DESC) AS rn
    FROM your_table_AuditHistory
    WHERE ChangeType = 'Migration_Update' AND ChangeInitiator = 'SystemUser'
)
-- 恢复原始数据
UPDATE t
SET t.column_1 = lb.column_1,
    t.UpdatedBy = lb.UpdatedBy,
    t.UpdatedDate = lb.UpdatedDate
FROM your_table t
JOIN LatestMigrationBackup lb ON t.Id = lb.TableId
WHERE lb.rn = 1 AND t.column_1 = 1;

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