Backup and Restore Databases with CHECKSUM and VERIFYONLY

By Lori Brown | Backup and Restore

Aug 29

I found out that I have been missing something from our regular database backups that I had no idea that I should have been using all along.  I know about verifying your backup file and have incorporated into our standard maintenance routines one that will periodically test backups by restoring using VERIFYONLY.  However, I totally missed also having CHECKSUM specified when creating backup files.  Ugh!!  Not sure how that happened but I am totally onboard with it now.  Better late than never!

**And now….DBA 101!!!!**  If you are not already, you should be performing regular database backups.  I generally take full database backups every day along with both differential and transaction log backups throughout the day.  I will generally space out the differential and tlog backups according to our client’s specific requirements for downtime and data loss.

So, let’s take a step back and make sure that we all understand what these backup and restore options are. 

In the backup GUI the verify backup and CHECKSUM options are in the Reliability options:

** Note:  I am using the most recent version of SSMS.  Older versions may not have this option.  In that case, you will have to include CHECKSUM using TSQL.

In TSQL it looks like this:

Here is what Microsoft says about both reliability options:

CHECKSUM specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

RESTORE VERIFYONLY verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable.

So these are two different things and both contribute towards you knowing that you have a good backup file and that the file can be reliably restored.


I thought I would do a small test to see how long it takes to do the backups with 3 different tests.  1) Backup with the COMPRESSION, CHECKSUM and VERIFYONLY options, 2) Backup with COMPRESSION and CHECKSUM options and 3) Backup with COMPRESSION only.  It is my understanding that CHECKSUM can take more CPU so I wanted to see what would happen.  I also performed all tests using a small (2GB) database and a larger (98GB) database.  I used a SQL 2016 instance with 4 CPU’s and 24GB of memory for both backups and restores.  The disks are all solid state storage and are pretty quick.  I did do backups and restores to disks that are separate from the actual database files.  Isn’t best practice nice!?!?  Results are as follows:


Here is the TSQL for the backups:

And here are the results:

As you can see, it looks like doing CHECKSUMs does not add too much CPU load but doing a VERIFYONLY does.  This is why in our weekly maintenance routines we verify large backups during maintenance times or on a separate server somewhere in an effort to prevent the VERIFYONLY workload from affecting users.


Restores were done on the same system.  I used the same files for both types of restores, one with CHECKSUM and one without.  Notice from the commands below that by default, if you don’t tell SQL to restore with checksum, regardless of if you did checksums when you backed it up, it will just skip the checksums.

And here are the results:

So what happens if you try to restore a database with checksum when it was not backed up with checksum?  You get an error:

Obviously you need to know how to figure out if your backup has CHECKSUMs done so that you don’t get an error.  Simply do a RESTORE HEADERONLY and look for the HasBackupChecksums column.  A value of 1 means that there are CHECKSUMs and 0 means there are not.

Take a little time to include CHECKSUMs in your backups so that you are more certain that your backup is a good one.  I know that SQLRX will make sure that backups in our monitored servers have it included.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

  • […] Lori Brown reminds us to perform checksums and verify backups on completion: […]

  • >