在[msdb].[dbo].restorehistory创建触发器失效问题咨询

需求与测试场景 #

• 目标：将Prod数据库恢复至Test环境时，自动修改特定表的字段值（例如把字段中的'PROD'替换为'Test'）
• 测试操作：创建仅含两张表的测试库，尝试通过在[msdb].[dbo].restorehistory表上创建INSERT触发器，模拟备份恢复后自动执行字段修改逻辑

问题描述 #

执行数据库恢复操作后，触发器未触发，字段修改逻辑未执行，附上触发器代码：

USE [msdb]
GO
/****** Object:  Trigger [dbo].[TriggerA]    Script Date: 12/24/2022 8:22:59 PM ******/

CREATE TRIGGER [dbo].[TriggerA]
ON [msdb].[dbo].restorehistory
FOR INSERT
AS

DECLARE @DatabaseName AS VARCHAR(100)

SELECT @DatabaseName = DESTINATION_DATABASE_NAME
FROM INSERTED

IF @DatabaseName='TestDB'

BEGIN
    UPDATE [TestDB].[dbo].<TABLE_X>
        SET FIELD_A     = REPLACE(FIELD_A,'_character_to_replace_','_replace_with_this_character_'), 
            FIELD_B     = REPLACE(FIELD_B,'_character_to_replace_','_replace_with_this_character_') 
        WHERE 
            FIELD_A LIKE '%_character_to_replace_%'

END

排查要点 #

• 权限不足：检查触发器所有者是否具备ALTER ANY TRIGGER权限，以及对TestDB.dbo.<TABLE_X>的UPDATE权限。权限不够会导致触发器触发失败，或执行UPDATE时直接报错。
• 恢复操作未成功：SQL Server仅在恢复操作完全提交后，才会向restorehistory表插入记录。先查询restorehistory表是否存在TestDB的恢复记录，若没有则说明恢复本身失败，触发器自然不会触发。
• 触发器不支持多记录场景：当前触发器用变量获取INSERTED表中的数据库名，若同时恢复多个数据库，INSERTED表会有多条记录，变量只会取最后一条的值，导致TestDB的修改逻辑漏执行。
• 目标库状态异常：恢复后若TestDB处于离线、只读或还原中状态，触发器内的UPDATE语句会执行失败。默认情况下触发器不会抛出错误，看起来像是未触发，实际是UPDATE执行失败。可添加日志表记录执行情况，排查具体原因。
• 触发器被禁用：执行以下语句检查触发器状态，若is_disabled为1，说明触发器被禁用，需手动启用：

  SELECT name, is_disabled FROM msdb.sys.triggers WHERE name = 'TriggerA'
  

更优实现方案 #

方案1：修复现有触发器 #

修改触发器以支持多库恢复场景，添加错误处理与执行日志，避免踩坑：

USE [msdb]
GO
-- 先创建日志表，用于记录触发器执行情况
CREATE TABLE dbo.TriggerLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    DatabaseName VARCHAR(100),
    OperationTime DATETIME DEFAULT GETDATE(),
    Status VARCHAR(50),
    ErrorMessage VARCHAR(MAX)
);
GO

-- 修改触发器
ALTER TRIGGER [dbo].[TriggerA]
ON [msdb].[dbo].restorehistory
FOR INSERT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

-- 遍历所有恢复的目标库，仅处理TestDB
DECLARE @DatabaseName VARCHAR(100);
DECLARE db_cursor CURSOR FOR
SELECT DISTINCT DESTINATION_DATABASE_NAME
FROM INSERTED
WHERE DESTINATION_DATABASE_NAME = 'TestDB';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- 先确认TestDB处于在线状态
        IF DATABASEPROPERTYEX(@DatabaseName, 'Status') = 'ONLINE'
        BEGIN
            UPDATE [TestDB].[dbo].<TABLE_X>
            SET FIELD_A = REPLACE(FIELD_A, 'PROD', 'Test'),
                FIELD_B = REPLACE(FIELD_B, 'PROD', 'Test')
            WHERE FIELD_A LIKE '%PROD%' OR FIELD_B LIKE '%PROD%';
            
            -- 记录成功日志
            INSERT INTO msdb.dbo.TriggerLog (DatabaseName, Status)
            VALUES (@DatabaseName, '字段修改成功');
        END
        ELSE
        BEGIN
            INSERT INTO msdb.dbo.TriggerLog (DatabaseName, Status)
            VALUES (@DatabaseName, '目标库未在线，跳过修改');
        END
    END TRY
    BEGIN CATCH
        -- 捕获错误并记录
        INSERT INTO msdb.dbo.TriggerLog (DatabaseName, Status, ErrorMessage)
        VALUES (@DatabaseName, '字段修改失败', ERROR_MESSAGE());
    END CATCH

    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

方案2：用SQL Server代理作业替代触发器 #

依赖系统表的触发器可能因系统操作时机问题失效，用代理作业更可靠：

1. 新建代理作业，作业步骤写入字段修改逻辑：

USE [TestDB]
GO
UPDATE [dbo].<TABLE_X>
SET FIELD_A = REPLACE(FIELD_A, 'PROD', 'Test'),
    FIELD_B = REPLACE(FIELD_B, 'PROD', 'Test')
WHERE FIELD_A LIKE '%PROD%' OR FIELD_B LIKE '%PROD%';

2. 恢复数据库后，手动启动该作业，或把恢复命令与启动作业的命令写在同一段脚本中：

-- 恢复数据库
RESTORE DATABASE TestDB FROM DISK = 'C:\Backup\ProdDB.bak'
WITH REPLACE, RECOVERY;

-- 启动代理作业
EXEC msdb.dbo.sp_start_job @job_name = 'UpdateTestDBFields';

方案3：恢复后直接执行修改脚本 #

最直接的方式，无需触发器或作业，恢复完成后立即执行字段修改：

-- 恢复数据库
RESTORE DATABASE TestDB FROM DISK = 'C:\Backup\ProdDB.bak'
WITH REPLACE, RECOVERY;

-- 执行字段修改
USE TestDB;
GO
UPDATE [dbo].<TABLE_X>
SET FIELD_A = REPLACE(FIELD_A, 'PROD', 'Test'),
    FIELD_B = REPLACE(FIELD_B, 'PROD', 'Test')
WHERE FIELD_A LIKE '%PROD%' OR FIELD_B LIKE '%PROD%';
GO

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