The Scariest Lie We Believed About Backup Verifications

Have you ever done something for a long time believing it to be true but actually isn’t? You know, something like going over 10% of the speed limit when you’re driving and thinking that you’re not over speeding? I’m guilty of this myself. But what’s even worse is that there are a lot of information on the internet that tell you something is deemed true when it actually isn’t. Let me explain.

When you create a database maintenance plan used for running database backups, one of the options you’ll see is to verify the integrity of the generated backup. Refer to the screenshot above as an example. The check box Verify backup integrity runs the RESTORE VERIFYONLY command immediately after the BACKUP DATABASE or BACKUP LOG command completes. The RESTORE VERIFYONLY command “verifies that the backup set is complete and all volumes are readable and that some header fields of database pages, such as the page ID (as if it were about to write the data).” The one thing that is missed the most is the information about backup checksum. Referring to the list of checks that the RESTORE VERIFYONLY command performs: “checksum (if present on the media),” emphasis mine. This means that the RESTORE VERIFYONLY command will only perform checksum verification if the backup media also contains the checksum verification.

To illustrate, I created a sample database named test that I will use for this purpose. I’ve also intentionally corrupted a page in the database to see how the RESTORE VERIFYONLY command behaves. I’ve created a simple database maintenance plan that will take a backup of the test database and verifies the backup integrity. I’ve also taken the generated T-SQL command to further illustrate the parameters used for both the BACKUP DATABASE command and the RESTORE VERIFYONLY command.

BACKUP DATABASE [test] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test_backup_2015_07_25_131110_1918625.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test_backup_2015_07_25_131110_1918625',
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
DECLARE @backupSetId AS INT
SELECT @backupSetId = position FROM msdb..backupset
WHERE database_name=N'test' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name=N'test' )
IF @backupSetId IS NULL BEGIN RAISERROR(N'Verify failed. Backup information for database ''test'' not found.', 16, 1) END
RESTORE VERIFYONLY
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test_backup_2015_07_25_131110_1918625.bak'
WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

Running the database maintenance plan on my sample database with the RESTORE VERIFYONLY command yield the following results.

VerifyBackupIntegrity_TSQL

 

The scary thing about this is that YOU BELIEVED that the backup was successful and that verifying the backup was also successful. All you did was to check the status of the SQL Server Agent job execution and verified whether or not the job completed successfully. You, then, go on doing your usual task, thinking that you have a good, solid, working backup. You might be in for a surprise.

I did mention that I intentionally corrupted the sample database to prove the point. I ran DBCC CHECKDB on the sample database which reported the corrupted page.

CHECKDB_testDB

 

In a previous blog post, I mentioned how to properly generate backups using the CHECKSUM option. If you run your backups using the CHECKSUM option, your RESTORE VERIFYONLY command will automatically run the checksum verification because the backup media has it. Here’s the same T-SQL statement that has the CHECKSUM option in the BACKUP DATABASE command. Note that the backup failed because the sample database contained a corrupted page.

VerifyBackupIntegrity2_TSQL

 

If I was working with a valid, non-corrupted database in a backup media, the RESTORE VERIFYONLY command can run a corresponding checksum verification.

VerifyBackupIntegrity3_TSQL

Call To Action

I would strongly recommend checking all of your backup jobs – be it in your maintenance plans or custom scripts – to make sure that you are not just taking backups for the sake of taking backups. You don’t want to be in a situation where a backup that you thought was valid was completely useless simply because you relied on the RESTORE VERIFYONLY command.

Posted on
Category: SQL Server
...

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

2 thoughts on “The Scariest Lie We Believed About Backup Verifications”

  1. This is awesome information, I always wondered about that checkbox, thanks Edwin. Curious to know why Microsoft would’ve put that option in the Maintenance Plan configurations if it does nothing.

    If I’m not mistaken, Ola Hallengren’s scripts also address this issue with his backup solutions.

    Reply

    • Sean,

      I think it’s because Microsoft assumes that since its in the documentation for RESTORE VERIFYONLY, SQL Server DBAs will know what it is for. And this is no different from other software vendors.

      It’s not that it does nothing. In fact, it does exactly what it was intended to do – does a checksum verification on the backup “if present on the backup media.”

      That’s why I try my best to educate the community about the risks of not really paying attention to documentation.

      Aren’t you glad Ola’s scripts address this?

      Thanks for reading my blog.

      Reply