We left off the last post with 2 equally corrupted databases. One database had page checksums enabled and the other did not.
We learned that upon reading the corrupt page, the checksum was recalculated, compared, and if unmatched, threw an exception. But what if the page that was corrupted isn’t read for a period of time? Maybe the page that has been corrupted is part of an older record that isn’t often read. Certainly a DBCC CheckDB command will find it, but we want to find out even sooner. It turns out the page checksums are again able to help us as we can ask SQL Server to validate every checksum during a database backup.
BACKUP WITH CHECKSUM
Since a full backup reads every data page it makes sense that we can ask SQL Server to calculate and compare each checksum during this operation. Even a differential backup reads all of the pages for any extent (a group of 8 data pages) if any one page in it changes so it will validate some pages that didn’t change.
There are 2 methods to include the checksum verification during a backup. In code add the WITH CHECKSUM option. In the SSMS UI it is an extra checkbox.
BACKUP DATABASE [MyDB] TO DISK = N'C:\MyDB.bak' WITH CHECKSUM
Let’s take some backups of our 2 databases now to see how SQL Server reacts.
BACKUP DATABASE IsItSafe TO DISK = 'C:\Backups\IsItSafe1.bak' BACKUP DATABASE HowCanIKnow TO DISK = 'C:\Backups\HowCanIKnow1.bak'
The 2 commands act exactly the same. Neither attempts to validate any checksums and both successfully complete.
BACKUP DATABASE IsItSafe TO DISK = 'C:\Backups\IsItSafe2.bak' WITH CHECKSUM BACKUP DATABASE HowCanIKnow TO DISK = 'C:\Backups\HowCanIKnow2.bak' WITH CHECKSUM
This pair of commands does attempt to validate checksums. On the first command there are no checksums to validate so it succeeds. The second command however, does have checksums and does fail.
Msg 3043, Level 16, State 1, Line 11 BACKUP 'HowCanIKnow' detected an error on page (1:336) in file 'C:\DATA\HowCanIKnow.mdf'. Msg 3013, Level 16, State 1, Line 11 BACKUP DATABASE is terminating abnormally.
This failure during the backup operation can be a very effective early warning system.
There is some overhead associated with validating the checksums during a backup operation so make sure the system can handle this extra work before enabling the feature.
The next post in the series will cover a few final odds and ends about page verification before using DBCC CheckDB to remove the corrupt page from the databases.
One final note: Almost everyone uses the free maintenance plans offered by Ola Hallengren on his website, https://ola.hallengren.com. By default, his program does not calculate checksums on backups.