SQL Server备份恢复While循环重复打印问题求助

问题现象 #

实现SQL Server数据库备份恢复的While循环逻辑时，第一个IF语句会重复打印恢复命令。

恢复场景说明 #

• 场景2 - 完整+差异备份恢复：先恢复完整备份（带NORECOVERY），再恢复差异备份（带RECOVERY）
• 场景3 - 完整+日志备份恢复：先恢复完整备份（带NORECOVERY），再按顺序恢复所有日志备份（除最后一个用RECOVERY外，其余用NORECOVERY）
• 场景4 - 完整+差异+日志备份恢复：先恢复完整备份（NORECOVERY）→最新差异备份（NORECOVERY）→所有日志备份（最后一个用RECOVERY，其余用NORECOVERY）
  注意：所有恢复流程的最后一个文件必须设置为RECOVERY

原代码 #

DECLARE @Counter INT 
SET 
  @Counter = 1 
  /*EXEC master..sp_executesql @FullBackupSQL */
  WHILE (
    @Counter <= (
      SELECT 
        COUNT(*) 
      FROM 
        Files
    )
  ) BEGIN 
SELECT 
  * 
FROM 
  Files 
WHERE 
  @Counter = ID 
SET 
  @Counter = @Counter + 1 IF @DiffBackupFile IS NOT NULL 
  AND @FullBackupFile IS NOT NULL 
  AND @LogbackupFile IS NULL BEGIN 
SET 
  @FullBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy + '  FROM DISK = ''' + @FullBackupFile + ''' WITH  NORECOVERY,  MOVE ''' + @DatabaseName + ''' TO ''' + @FullBackupFileCopy + ''', MOVE ''' + @FullBackupFileCopyldf + ''' 
 TO ''' + @FullBackupFileCopylocationldf + ''' , NORECOVERY,NOUNLOAD, REPLACE, STATS = 5' PRINT '139,' + @FullBackupSQL 
  /*EXEC master..sp_executesql @FullBackupSQL */
SET 
  @DiffBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy + ' FROM  DISK = ''' + @DiffBackupFile + ''' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5' PRINT '143,' + @DiffBackupSQL 
  /* EXEC master..sp_executesql @DiffBackupSQL */
SET 
  @Counter = @Counter + 1 END IF @FullBackupFile IS NOT NULL 
  AND @LogbackupFile IS NOT NULL 
  AND @DiffBackupFile IS NULL BEGIN 
SET 
  @FullBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy + '  FROM DISK = ''' + @FullBackupFile + ''' WITH  NORECOVERY,  MOVE ''' + @DatabaseName + ''' TO ''' + @FullBackupFileCopy + ''', MOVE ''' + @FullBackupFileCopyldf + ''' 
 TO ''' + @FullBackupFileCopylocationldf + ''' , NORECOVERY,NOUNLOAD, REPLACE, STATS = 5' PRINT '152,' + @FullBackupSQL 
  /*EXEC master..sp_executesql @FullBackupSQL */
SET 
  @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM  DISK = ''' + @LogbackupFile + ''' WITH FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5' PRINT '156,' + @LogBackupSQL 
  /*  EXEC master..sp_executesql @LogBackupSQL */
SET 
  @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM  DISK = ''' + @LogbackupFile + ''' WITH FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5' PRINT '160,' + @LogBackupSQL 
  /*  EXEC master..sp_executesql @LogBackupSQL */
  END IF @FullBackupFile IS NOT NULL 
  AND @LogbackupFile IS NOT NULL 
  AND @DiffBackupFile IS NOT NULL BEGIN 
SET 
  @FullBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy + '  FROM DISK = ''' + @FullBackupFile + ''' WITH  NORECOVERY,  MOVE ''' + @DatabaseName + ''' TO ''' + @FullBackupFileCopy + ''', MOVE ''' + @FullBackupFileCopyldf + ''' 
 TO ''' + @FullBackupFileCopylocationldf + ''' , NORECOVERY,NOUNLOAD, REPLACE, STATS = 5' PRINT '171,' + @FullBackupSQL 
  /*EXEC master..sp_executesql @FullBackupSQL */
SET 
  @DiffBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy + ' FROM  DISK = ''' + @DiffBackupFile + ''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5' PRINT '175,' + @DiffBackupSQL 
  /* EXEC master..sp_executesql @DiffBackupSQL */
SET 
  @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM  DISK = ''' + @LogbackupFile + ''' WITH FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5' PRINT '179,' + @LogBackupSQL 
  /*  EXEC master..sp_executesql @LogBackupSQL */
SET 
  @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM  DISK = ''' + @LogbackupFile + ''' WITH FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5' PRINT '183,' + @LogBackupSQL 
  /*  EXEC master..sp_executesql @LogBackupSQL */
SET 
  @Counter = @Counter + 1 END END

问题根源分析 #

1. 变量未正确赋值：循环内仅执行SELECT * FROM Files WHERE @Counter = ID，但未将查询结果赋值给@FullBackupFile、@DiffBackupFile、@LogbackupFile等变量，导致这些变量始终保留初始值或旧值，每次循环都会触发相同的IF条件，重复生成打印命令。
2. 计数器逻辑错误：第一个IF分支内额外执行SET @Counter = @Counter +1，导致循环计数器跳过部分记录，同时可能让循环多次进入同一条件分支。
3. 日志备份逻辑错误：场景3中重复生成相同的NORECOVERY日志恢复语句，未区分最后一个日志文件；场景4中虽区分了RECOVERY，但未基于Files表的实际记录顺序处理。
4. 多IF分支无互斥：三个IF分支未使用ELSE连接，若变量值同时满足多个条件，会触发多个分支执行，导致重复打印。

修复方案及代码 #

核心修复点 #

• 在循环内将Files表的查询结果赋值给对应变量，确保每次循环获取当前记录的备份信息
• 移除分支内额外的计数器递增，仅保留循环顶部的计数器更新
• 使用IF ELSE IF结构实现分支互斥，避免多分支同时触发
• 基于Files表的记录顺序，判断当前是否为最后一个文件，动态设置NORECOVERY/RECOVERY
• 日志备份按顺序处理，仅最后一个日志用RECOVERY

修复后代码 #

DECLARE @Counter INT, @TotalFiles INT
DECLARE @BackupType VARCHAR(20), @BackupPath VARCHAR(500)
DECLARE @DatabaseName VARCHAR(100), @DatabaseNameCopy VARCHAR(100)
DECLARE @FullBackupFileCopy VARCHAR(500), @FullBackupFileCopylocationldf VARCHAR(500)

SET @Counter = 1
SELECT @TotalFiles = COUNT(*) FROM Files

WHILE @Counter <= @TotalFiles
BEGIN
    -- 获取当前备份文件的类型和路径（假设Files表包含BackupType和BackupPath字段）
    SELECT @BackupType = BackupType, @BackupPath = BackupPath
    FROM Files
    WHERE ID = @Counter

    -- 判断是否为最后一个文件
    DECLARE @IsLastFile BIT = CASE WHEN @Counter = @TotalFiles THEN 1 ELSE 0 END

    IF @BackupType = 'Full'
    BEGIN
        -- 完整备份恢复，仅当是唯一文件时用RECOVERY，否则用NORECOVERY
        DECLARE @RecoveryOption VARCHAR(20) = CASE WHEN @TotalFiles = 1 THEN 'RECOVERY' ELSE 'NORECOVERY' END
        DECLARE @FullBackupSQL NVARCHAR(MAX) = 
            'RESTORE DATABASE ' + QUOTENAME(@DatabaseNameCopy) + 
            ' FROM DISK = ''' + @BackupPath + ''' WITH ' + @RecoveryOption + ', ' +
            'MOVE ''' + QUOTENAME(@DatabaseName) + ''' TO ''' + @FullBackupFileCopy + ''', ' +
            'MOVE ''' + QUOTENAME(@DatabaseName + '_log') + ''' TO ''' + @FullBackupFileCopylocationldf + ''', ' +
            'NOUNLOAD, REPLACE, STATS = 5'
        PRINT 'Full restore command: ' + @FullBackupSQL
        -- EXEC master..sp_executesql @FullBackupSQL
    END
    ELSE IF @BackupType = 'Diff'
    BEGIN
        -- 差异备份恢复，最后一个文件用RECOVERY，否则用NORECOVERY
        DECLARE @DiffRecoveryOption VARCHAR(20) = CASE WHEN @IsLastFile = 1 THEN 'RECOVERY' ELSE 'NORECOVERY' END
        DECLARE @DiffBackupSQL NVARCHAR(MAX) = 
            'RESTORE DATABASE ' + QUOTENAME(@DatabaseNameCopy) + 
            ' FROM DISK = ''' + @BackupPath + ''' WITH FILE = 1, ' + @DiffRecoveryOption + ', ' +
            'NOUNLOAD, STATS = 5'
        PRINT 'Diff restore command: ' + @DiffBackupSQL
        -- EXEC master..sp_executesql @DiffBackupSQL
    END
    ELSE IF @BackupType = 'Log'
    BEGIN
        -- 日志备份恢复，最后一个文件用RECOVERY，否则用NORECOVERY
        DECLARE @LogRecoveryOption VARCHAR(20) = CASE WHEN @IsLastFile = 1 THEN 'RECOVERY' ELSE 'NORECOVERY' END
        DECLARE @LogBackupSQL NVARCHAR(MAX) = 
            'RESTORE LOG ' + QUOTENAME(@DatabaseNameCopy) + 
            ' FROM DISK = ''' + @BackupPath + ''' WITH FILE = 1, ' + @LogRecoveryOption + ', ' +
            'NOUNLOAD, STATS = 5'
        PRINT 'Log restore command: ' + @LogBackupSQL
        -- EXEC master..sp_executesql @LogBackupSQL
    END

    SET @Counter = @Counter + 1
END

代码说明 #

1. 新增@BackupType和@BackupPath变量，每次循环从Files表获取当前备份文件的类型和路径，确保逻辑基于当前记录。
2. 通过@IsLastFile判断是否为最后一个文件，动态设置恢复模式，符合场景要求。
3. 使用QUOTENAME函数处理数据库名，避免特殊字符导致的语法错误。
4. 采用IF ELSE IF结构确保每次循环仅执行一个分支，避免重复触发。
5. 移除分支内额外的计数器递增，统一在循环末尾更新计数器，保证循环顺序正确。

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