SQL Server Restore Verify Only Loop with Update-CodePudding

I'm running a Restore Verify Only loop through a table, but I need it to UPDATE a field in that table if it's successful. Here is my code:

DECLARE @Path NVARCHAR(max)
DECLARE @DatabaseName NVARCHAR(100)
DECLARE @NSql NVARCHAR(1000)
DECLARE @Update NVARCHAR(200)
DECLARE @DB_Text NVARCHAR(50)= 'Backup has been confirmed for'

BEGIN
DECLARE
     @DatabaseId INT = 1,
     @NumberOfDBs INT
SELECT  @NumberOfDBs= COUNT(*) FROM dbo.RestoreVerifyDatabases
WHILE @DatabaseId<=  @NumberOfDBs
BEGIN
    SELECT * FROM dbo.RestoreVerifyDatabases WHERE DatabaseId=  @DatabaseId         
    SET  @DatabaseId = @DatabaseId   1
END


SET @Path = (SELECT LastBackupFileName FROM RestoreVerifyDatabases WHERE DatabaseId = 
@DatabaseId)
SET @DatabaseName = (SELECT DatabaseName FROM RestoreVerifyDatabases WHERE DatabaseId = 
@DatabaseId)

SET @NSql = N'SELECT LastBackupFileName
FROM RestoreVerifyDatabases 
WHERE DatabaseName = @DatabaseName
AND DatabaseId = @DatabaseId'

EXEC sp_executesql @NSql

IF @DatabaseId IS NULL
    BEGIN 
         RAISERROR(N'Verify failed. Backup information for database N''@DatabaseName'' not 
 found.', 16, 1) 
    END

RESTORE VERIFYONLY 
FROM @Path
WITH FILE = @DatabaseId,  checksum

SET @Update= N'UPDATE RestoreVerifyDatabases
SET Confirmed = @DB_Text   @DatabaseName
WHERE DatabaseID = @DatabaseId'

EXEC sp_executesql @Update

END

The looping and calling of Restore Verify Only works fine but the UPDATE is not getting called. I've fought with this problem for days now, please help.

CodePudding user response：

There's a few things wrong with your code as far as I can see. As my comment indicated, you run a loop over the @databaseID, and then immediately increase the id with 1 within the loop.

This means your @databaseId simply will increase until it reaches the maximum value of the id, and then exits the loop. The rest of your code will then execute once (only for that ID).

That happens here in your code:

SELECT  @NumberOfDBs= COUNT(*) FROM dbo.RestoreVerifyDatabases
WHILE @DatabaseId<=  @NumberOfDBs
BEGIN
    SELECT * FROM dbo.RestoreVerifyDatabases WHERE DatabaseId=  @DatabaseId         
    SET  @DatabaseId = @DatabaseId   1
END

-- Bunch of other code

So @databaseID goes from 1 to 50 (just a random number) within that loop, then exists with a value of 50. None of the underlying code ever gets to see any value other than 50.

To fix that, the code should look like this:

SELECT  @NumberOfDBs= COUNT(*) FROM dbo.RestoreVerifyDatabases
WHILE @DatabaseId<=  @NumberOfDBs
BEGIN
    SELECT * FROM dbo.RestoreVerifyDatabases WHERE DatabaseId=  @DatabaseId         

    -- Bunch of other code
    SET  @DatabaseId = @DatabaseId   1
END

This would ensure the "bunch of other code" processes @databaseid = 1, then processes @databaseid = 2, etc. until @databaseid = 50.

You can also remove this line:

SELECT * FROM dbo.RestoreVerifyDatabases WHERE DatabaseId=  @DatabaseId

I suspect you have it for debugging purposes, but it doesn't really do anything.

These lines can be simplified:

SET @Path = (SELECT LastBackupFileName FROM RestoreVerifyDatabases WHERE DatabaseId = 
@DatabaseId)
SET @DatabaseName = (SELECT DatabaseName FROM RestoreVerifyDatabases WHERE DatabaseId = 
@DatabaseId)

as:

    SELECT @Path = LastBackupFileName, @DatabaseName = DatabaseName FROM RestoreVerifyDatabases WHERE DatabaseId = @DatabaseId

You also have a RAISEERROR. I suspect that is because you do a count(*), rather than selecting actually existing @databaseids. This works in an ideal scenario, where databases never get deleted. In the reasl world, though, you'd go from databaseid 1 to 5, because 2, 3, and 4 were removed. Your count will still be 50 databases, but you'd miss all databases with ids above the count due to these gaps. You'd be trying to process the databaseid that no longer exists, and miss the ones with an id > 50.

You could instead write your loop to do something like the following:

SELECT @DatabaseID = MIN(DatabaseID) FROM dbo.RestoreVerifyDatabases
WHILE @datbaseID IS NOT NULL
BEGIN
    -- Do stuff

    SELECT @DatabaseID = MIN(DatabaseID) FROM dbo.RestoreVerifyDatabases WHERE databaseID > @databaseID
END

Finally, your dynamic code isn't working. You are adding literal text rather than the value of the parameters. If I print the value of @Update at the end, it shows as:

UPDATE RestoreVerifyDatabases
        SET Confirmed = @DB_Text   @DatabaseName
        WHERE DatabaseID = @DatabaseId

Your code should be something like:

SET @Update= CONCAT('UPDATE RestoreVerifyDatabases SET Confirmed = ''', @DB_Text, ' ',  @DatabaseName, ''' WHERE DatabaseID = ',  @DatabaseId)

Which outputs:

UPDATE RestoreVerifyDatabases SET Confirmed = 'Backup has been confirmed for Last' WHERE DatabaseID = 2