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.  https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017 

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.

Testing

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:

Backups

Here is the TSQL for the backups:

-- Backup W Compression, Checksum and Verify (2GB database)
BACKUP DATABASE [SQLRX] TO  DISK = N'R:MSSQLBackupsSQLRX_Full_Cksum_Vfy.bak' 
WITH NOFORMAT, INIT,  NAME = N'SQLRX-Full Database Backup', 
COMPRESSION, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'SQLRX' and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'SQLRX' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''SQLRX'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'R:MSSQLBackupsSQLRX_Full_Cksum_Vfy.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO
-- Backup W Compression and Checksum (2GB database)
BACKUP DATABASE [SQLRX] TO  DISK = N'R:MSSQLBackupsSQLRX_Full_Cksum.bak' 
WITH NOFORMAT, INIT,  NAME = N'SQLRX-Full Database Backup', 
COMPRESSION, CHECKSUM
GO
-- Backup W Compression Only (2GB database)
BACKUP DATABASE [SQLRX] TO  DISK = N'R:MSSQLBackupsSQLRX_Full.bak' 
WITH NOFORMAT, INIT,  NAME = N'SQLRX-Full Database Backup', 
COMPRESSION
GO
-- Backup W Compression, Checksum and Verify (98GB database)
BACKUP DATABASE [LargeSQLRXdb] TO  DISK = N'R:MSSQLBackupsLargeSQLRXdb_Full_Cksum_Vfy.bak' 
WITH NOFORMAT, INIT,  NAME = N'LargeSQLRXdb-Full Database Backup', 
COMPRESSION, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'LargeSQLRXdb' and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'LargeSQLRXdb' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''LargeSQLRXdb'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'R:MSSQLBackupsLargeSQLRXdb_Full_Cksum_Vfy.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO
-- Backup W Compression and Checksum (98GB database)
BACKUP DATABASE [LargeSQLRXdb] TO  DISK = N'R:MSSQLBackupsLargeSQLRXdb_Full_Cksum.bak' 
WITH NOFORMAT, INIT,  NAME = N'LargeSQLRXdb-Full Database Backup', 
COMPRESSION, CHECKSUM
GO
-- Backup W Compression Only (98GB database)
BACKUP DATABASE [LargeSQLRXdb] TO  DISK = N'R:MSSQLBackupsLargeSQLRXdb_Full.bak' 
WITH NOFORMAT, INIT,  NAME = N'LargeSQLRXdb-Full Database Backup', 
COMPRESSION
GO

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

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.

--W Checksum (2GB database) -  12sec, 48% CPU 
USE [master]
RESTORE DATABASE [SQLRX] FROM  DISK = N'R:MSSQLBackupsSQLRX_Full_Cksum_Vfy.bak' WITH  FILE = 1,  CHECKSUM, NOUNLOAD,  REPLACE
GO

--WO Checksum (2GB database) -  12sec, 36% CPU
USE [master]
RESTORE DATABASE [SQLRX] FROM  DISK = N'R:MSSQLBackupsSQLRX_Full_Cksum_Vfy.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE
GO
There was virtually no difference in total time to restore but there was a big difference in the amount of CPU used when checksums are used and not.
--W Checksum (98GB database) -  8min 24sec, 46% CPU
USE [master]
RESTORE DATABASE [LargeSQLRXdb] FROM  DISK = N'R:MSSQLBackupsLargeSQLRXdb_Full_Cksum.bak' WITH  FILE = 1,  CHECKSUM, NOUNLOAD,  REPLACE
GO
--WO Checksum (98GB database) -  7min 18sec, 37% CPU
USE [master]
RESTORE DATABASE [LargeSQLRXdb] FROM  DISK = N'R:MSSQLBackupsLargeSQLRXdb_Full_Cksum.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE
GO

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 SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

  • gautam says:

    Superb, I love your article, thanks for sharing with us.

  • >