Got it, let's break down how to handle this scenario. When running RESTORE VERIFYONLY, you need to reliably check if the backup verification succeeded, and stop your code if it didn't. Here are the two most practical approaches:

1. Use TRY...CATCH Block (Modern & Recommended) #

SQL Server's TRY...CATCH construct is the cleanest way to capture errors from RESTORE VERIFYONLY. If the verification fails (e.g., invalid backup file, missing path, corrupted data), the command will throw an error that gets caught in the CATCH block, where you can handle the failure and halt execution.

Here's a working example:

BEGIN TRY
    -- Run the backup verification
    RESTORE VERIFYONLY FROM DISK = 'c:\common\backups'
    
    -- If we reach here, verification succeeded
    PRINT '✅ Backup verification passed!'
    -- Add your follow-up logic here (e.g., restore operations, logging)
END TRY
BEGIN CATCH
    -- Capture and report the error details
    PRINT '❌ Backup verification failed: ' + ERROR_MESSAGE()
    
    -- Halt execution by re-throwing the original error (stops all subsequent code)
    THROW;
    -- Alternatively, exit the batch quietly without throwing an error:
    -- RETURN;
END CATCH

The THROW statement propagates the original error to the caller, ensuring no further code runs. If you prefer to exit silently, replace THROW with RETURN.

2. Check @@ERROR (Legacy Approach) #

Before TRY...CATCH was introduced, developers used the @@ERROR system variable to check for errors immediately after running a command. This still works, but it's less robust because @@ERROR resets after every subsequent statement.

Example:

-- Execute the verification command
RESTORE VERIFYONLY FROM DISK = 'c:\common\backups'

-- Check for errors immediately (don't add any code between the command and this check!)
IF @@ERROR <> 0
BEGIN
    PRINT '❌ Backup verification failed.'
    -- Raise a fatal error to halt execution
    RAISERROR('Backup verification failed. Execution stopped.', 16, 1)
    -- Or exit the batch with RETURN:
    -- RETURN;
END

-- Proceed only if verification succeeded
PRINT '✅ Verification complete. Continuing with logic...'
-- Your follow-up code here

Note: This method won't catch all error types (like severity level 20+ errors that terminate the connection), so TRY...CATCH is the better choice for most production scenarios.

Bonus: Handling Warnings #

In some cases, RESTORE VERIFYONLY might return warnings (e.g., backup created on a newer SQL Server version) without throwing an error. If you need to catch these, you'd have to use extended events or log the output to a table, but this is more advanced. For most use cases, focusing on error capture via the methods above is sufficient.

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