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.

[callout]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
[/callout]

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

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.

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.

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

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.