我正在尝试让我的 While 循环适用于某些场景？一些帮助表示赞赏-解网

问：

场景如下，但是我的 while 循环为第一个 if 语句保持两次打印文件。

第二种方案 - 恢复完整和差异逻辑顺序：无恢复的完整备份和带恢复的差异

第 3 种方案 - 还原完整日志和日志逻辑顺序：已满，无恢复，最后日志与恢复和无恢复前的日志

第 4 种方案 - 还原完整、差异和日志逻辑顺序：已满且无恢复和最新差异（无恢复）和“上次日志无恢复”和“上次日志无恢复”和“上次日志有恢复”之前的日志。

最后一个文件需要在恢复中

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

t-sql while-loop

0赞 Charlieface 3/1/2023

似乎无关紧要，此处未设置变量。并且您似乎正在两次还原相同的日志文件。一种选择是做所有文件，然后只做最后的SELECT * FROM FilesWITH NORECOVERYRESTORE DATABASE WITH RECOVERY

答：

0赞 Charlieface 3/1/2023 #1

循环的目的和目的完全不清楚：无论如何，您实际上并没有使用来自它们的数据。SELECT *

您需要做的就是使用然后使用最终版本恢复每个部分，以使数据库联机。WITH NORECOVERYRESTORE WITH RECOVERY

另请注意

RESTORE并且可以完全参数化，不需要.即使您确实需要它，您也必须确保使用 .BACKUPsp_executesqlQUOTENAME
确保您的对象名称是或，文件名是。sysnamenvarchar(128)nvarchar(255)
您还可以通过嵌套语句来简化代码（无论如何，如果没有完整备份，就无法还原差异）。IF
NOUNLOAD仅与设备相关。TAPE

IF @FullBackupFile IS NOT NULL 
BEGIN
    RESTORE DATABASE @DatabaseNameCopy
    FROM DISK = @FullBackupFile
    WITH NORECOVERY,
      MOVE @DatabaseName TO @FullBackupFileCopy,
      MOVE @FullBackupFileCopyldf TO @FullBackupFileCopylocationldf,
      NORECOVERY, REPLACE, STATS = 5;

     IF @DiffBackupFile IS NOT NULL
    BEGIN
        RESTORE DATABASE @DatabaseNameCopy
        FROM DISK = @DiffBackupFile
        WITH FILE = 1, NORECOVERY, STATS = 5;
    END;
END;

IF @LogbackupFile IS NOT NULL
BEGIN
    RESTORE LOG @DatabaseNameCopy
    FROM DISK = @LogbackupFile
      WITH FILE = 1, NORECOVERY, STATS = 5
END;

RESTORE DATABASE @DatabaseNameCopy
WITH RECOVERY;

如果需要遍历文件列表，最简单的方法是使用光标。

DECLARE @crsr CURSOR;
SET @crsr = CURSOR FAST_FORWARD FOR
    SELECT Filename
    FROM Files;

OPEN @crsr;

FETCH NEXT @crsr INTO @LogbackupFile;
WHILE @@FETCH_STATUS = 0
BEGIN
    RESTORE LOG @DatabaseNameCopy
    FROM DISK = @LogbackupFile
      WITH FILE = 1, NORECOVERY, STATS = 5

    FETCH NEXT @crsr INTO @LogbackupFile;
END;

上一个：SQL While 循环从 select 在另一个选择中

下一个：使用 while 循环创建计算度量值的 SQL 替代方法

我正在尝试让我的 While 循环适用于某些场景？一些帮助表示赞赏

I'm trying to get my While loop working with some scenarios? some help appreciated

评论