Always Backup WITH CHECKSUM?

When you’re specifying WITH CHECKSUM as you’re backing up databases, SQL Server will use checksums to help catch any inconsistencies with pages. This seems like a setting that you should always use and would expect to be a default setting. So why doesn’t SQL Server include it by default?

They Are Who We Thought They Were

I figured the extra processing work involved when using WITH CHECKSUM might be the reason but was curious to know more. It was time to dive into some documentation. I ended up on this page which had the answer I was looking for. Under the Backup Checksums section, it says:

“Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. Both the workload and the backup throughput may be affected. Therefore, using backup checksums is optional. When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system.”

I’m sure this is true; however, I can’t imagine the overhead not being worth more validation that your backups are good. I’m not saying the overhead would never be an issue but to me it seems highly unlikely.

With that said, let’s look at an example backup using WITH CHECKSUM.

Bouncing Checks

If we’re looking at a new query window in SSMS and want to backup the ExampleDB database WITH CHECKSUM, we can run the following:

BACKUP DATABASE [ExampleDB]
TO DISK = N'E:\Backup\ExampleDB_Full.bak' 
WITH CHECKSUM;
GO

That’s a bare bones example but it shows that you just need to add “WITH CHECKSUM” when performing the backup. If you’re in the SSMS GUI and backing up a database, tick the “Perform checksum before writing to media” checkbox:

Go ahead and tick the “Verify backup when finished” while you’re there. With this ticked, the header information in the backup will be validated.

Ain’t Nothing Like the Real Thing

Keep in mind there are no substitutes for fully testing your backups and restores. Knowing you have backups is great. Knowing you can restore them will allow you to sleep soundly.

Thanks for reading!