SQL WHILE循环重复输出备份文件路径问题求助

问题描述 #

使用WHILE循环从#TempTable_Log临时表读取不同事务日志备份路径，生成RESTORE LOG语句时，循环多次输出的路径完全相同，无法依次获取不同的备份文件路径。

原代码 #

DECLARE @Counter INT 
SET @Counter = 1 
WHILE ( @Counter <= (SELECT COUNT(*) FROM #TempTable_Log))
        --To handle gaps in the looping column value
        BEGIN 
        PRINT @Counter 
        SELECT @LogbackupFile = physical_device_name,  @LogBackupSQL = @LogbackupFile 
      FROM #TempTable_log
      WHERE backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
      AND backup_finish_date > @FullBackupDate 
      AND backup_finish_date <= @RestoreDate 
      SET @Counter = @Counter + 1 
      SET @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM  DISK 
      = ''' + @LogbackupFile + ''' WITH  FILE = 1,  NOUNLOAD,  STATS = 5,' 
     PRINT @LogBackupSQL 
END

实际输出 #

(3 rows affected)
1
RESTORE LOG ns_lots_of_vlfs_copy FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.KUSHALRATTU\MSSQL\Backup\ns_lots_of_vlfs_tran_03_10022023.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5,
2
RESTORE LOG ns_lots_of_vlfs_copy FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.KUSHALRATTU\MSSQL\Backup\ns_lots_of_vlfs_tran_03_10022023.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5,
3
RESTORE LOG ns_lots_of_vlfs_copy FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.KUSHALRATTU\MSSQL\Backup\ns_lots_of_vlfs_tran_03_10022023.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5,

预期输出 #

C:\Program Files\Microsoft SQL Server\MSSQL15.KUSHALRATTU\MSSQL\Backup\ns_lots_of_vlfs_tran_03_10022023.trn
C:\Program Files\Microsoft SQL Server\MSSQL15.KUSHALRATTU\MSSQL\Backup\ns_lots_of_vlfs_tran_02_10022023.trn
C:\Program Files\Microsoft SQL Server\MSSQL15.KUSHALRATTU\MSSQL\Backup\ns_lots_of_vlfs_tran_01_10022023.trn

问题根源 #

原代码每次循环都会查询所有符合条件的行，当查询返回多行时，变量@LogbackupFile会被最后一行的结果覆盖，因此每次循环输出的都是同一个路径。

解决方案 #

方案1：给临时表添加序号列，按序号逐行获取 #

先给临时表添加自增序号列（若临时表已创建）：

ALTER TABLE #TempTable_Log ADD RowNum INT IDENTITY(1,1)

修改循环代码，按序号匹配逐行读取：

DECLARE @Counter INT, @TotalRows INT
SET @Counter = 1

-- 先统计符合条件的总行数
SELECT @TotalRows = COUNT(*) 
FROM #TempTable_log
WHERE backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
  AND backup_finish_date > @FullBackupDate 
  AND backup_finish_date <= @RestoreDate

WHILE @Counter <= @TotalRows
BEGIN
    -- 按序号获取当前行的备份路径
    SELECT @LogbackupFile = physical_device_name
    FROM #TempTable_log
    WHERE RowNum = @Counter 
      AND backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
      AND backup_finish_date > @FullBackupDate 
      AND backup_finish_date <= @RestoreDate 

    -- 生成RESTORE语句并输出
    SET @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM DISK = ''' + @LogbackupFile + ''' WITH FILE = 1, NOUNLOAD, STATS = 5,'
    PRINT @LogBackupSQL
    
    SET @Counter = @Counter + 1
END

方案2：使用游标逐行处理（推荐） #

游标是SQL中专门用于逐行处理数据的对象，更适合此类场景：

DECLARE @LogbackupFile NVARCHAR(255), @LogBackupSQL NVARCHAR(MAX)

-- 定义游标，按备份时间排序确保恢复顺序正确
DECLARE LogBackupCursor CURSOR FOR
SELECT physical_device_name
FROM #TempTable_log
WHERE backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
  AND backup_finish_date > @FullBackupDate 
  AND backup_finish_date <= @RestoreDate 
ORDER BY backup_finish_date DESC -- 按备份完成时间倒序，或根据需求调整顺序

-- 打开游标并逐行读取
OPEN LogBackupCursor
FETCH NEXT FROM LogBackupCursor INTO @LogbackupFile

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成RESTORE语句并输出
    SET @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM DISK = ''' + @LogbackupFile + ''' WITH FILE = 1, NOUNLOAD, STATS = 5,'
    PRINT @LogBackupSQL
    
    -- 获取下一行数据
    FETCH NEXT FROM LogBackupCursor INTO @LogbackupFile
END

-- 关闭并释放游标
CLOSE LogBackupCursor
DEALLOCATE LogBackupCursor

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