循环执行Restore Verify Only时UPDATE语句未执行问题求助

核心问题 #

UPDATE语句不执行的核心原因是所有备份校验、更新逻辑全部写在了WHILE循环的作用域外面，循环执行逻辑完全错位：

• 现有WHILE循环内只做了两件事：查询当前ID对应的数据、给@DatabaseId自增，没有包含任何校验、更新逻辑
• 循环退出时@DatabaseId的值已经是总记录数+1，循环外查询@Path、@DatabaseName时匹配不到任何有效记录，后续逻辑要么查不到数据报错中断，要么操作的是不存在的行，自然不会触发更新。

其他连带逻辑错误 #

• 动态SQL写法错误：用sp_executesql执行的语句默认无法直接访问外层定义的变量，写在@NSql、@Update里的@DatabaseName、@DB_Text、@DatabaseId在动态SQL作用域内都是未声明状态，执行会直接报错
• 没有校验RESTORE VERIFYONLY的执行结果：现有逻辑不管校验成功失败都会尝试更新，且VERIFYONLY校验失败时会直接抛出错误终止批处理，后续代码根本无法执行
• 变量长度定义不合理：@Update定义为NVARCHAR(200)，遇到较长的备份路径、数据库名时会出现字符串截断，导致SQL执行失败
• 空值判断逻辑错误：判断@DatabaseId IS NULL完全无意义，@DatabaseId从初始值1开始一直自增，永远不会为NULL，真正需要判断的是查询到的备份路径、库名是否为空。

修正后可运行代码 #

DECLARE @Path NVARCHAR(MAX)
DECLARE @DatabaseName NVARCHAR(100)
DECLARE @NSql NVARCHAR(MAX)
DECLARE @Update NVARCHAR(MAX)
DECLARE @DB_Text NVARCHAR(50)= N'Backup has been confirmed for'
DECLARE @VerifyResult INT -- 存储校验操作的返回值，0为成功

BEGIN
    DECLARE
         @DatabaseId INT = 1,
         @NumberOfDBs INT
    SELECT  @NumberOfDBs = COUNT(*) FROM dbo.RestoreVerifyDatabases

    -- 把校验、更新逻辑全部移入WHILE循环内
    WHILE @DatabaseId <= @NumberOfDBs
    BEGIN
        -- 每次循环查询当前ID对应的备份信息
        SELECT 
            @Path = LastBackupFileName,
            @DatabaseName = DatabaseName
        FROM dbo.RestoreVerifyDatabases 
        WHERE DatabaseId = @DatabaseId

        -- 判断备份信息是否存在
        IF @Path IS NULL OR @DatabaseName IS NULL
        BEGIN 
             RAISERROR(N'Verify failed. Backup information for database N''%s'' not found.', 16, 1, @DatabaseName)
             SET @DatabaseId = @DatabaseId + 1
             CONTINUE -- 跳过当前无效记录，处理下一条
        END

        -- 执行备份校验，捕获返回值
        BEGIN TRY
            EXEC @VerifyResult = sp_executesql N'
            RESTORE VERIFYONLY 
            FROM @Path
            WITH FILE = @DatabaseId, CHECKSUM'
            , N'@Path NVARCHAR(MAX), @DatabaseId INT'
            , @Path = @Path, @DatabaseId = @DatabaseId
        END TRY
        BEGIN CATCH
            SET @VerifyResult = 1 -- 捕获到异常标记为校验失败
            PRINT N'数据库' + @DatabaseName + N'备份校验失败：' + ERROR_MESSAGE()
        END CATCH

        -- 仅校验成功时执行更新
        IF @VerifyResult = 0
        BEGIN
            SET @Update = N'UPDATE RestoreVerifyDatabases
            SET Confirmed = @DB_Text + @DatabaseName
            WHERE DatabaseID = @DatabaseId'
            -- 动态SQL显式传参
            EXEC sp_executesql @Update
                , N'@DB_Text NVARCHAR(50), @DatabaseName NVARCHAR(100), @DatabaseId INT'
                , @DB_Text = @DB_Text, @DatabaseName = @DatabaseName, @DatabaseId = @DatabaseId
        END

        SET @DatabaseId = @DatabaseId + 1
    END
END

注意：执行RESTORE相关操作需要对应SQL Server服务账号有备份文件的读取权限，否则校验会直接报权限错误。

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