Does using WITH CHECKSUM ensure a successful backup?

Question: I’m having an issue where our backups are getting corrupted somehow. I’m trying to prove that the backups are good when they’re initially created. My understanding is that using backup checksums means that if the backup succeeds, there were no corruptions found in the database. Is this correct?

Answer: Kind of.

When the WITH CHECKSUM option is used for a backup, the backup process will test the page checksums that exist on the data file pages being backed up. If a bad page checksum is found, the backup process will stop (by default – you can override this using WITH CONTINUE_AFTER_ERROR but I’d only do that in an emergency). So if the backup completes successfully, you know that there are no broken page checksums.

But does that mean there are no corruptions? Absolutely not.

Firstly, if the database was upgraded from a version where page checksums weren’t available or if page checksums were not enabled for some reason, then it’s possible that not all pages in the database actually have a page checksum (as a page checksum is only put on a data file page after page checksums are enabled and then the page is read into memory, altered, and written back out to disk). This means that using WITH CHECKSUM is not testing the I/O subsystem integrity of the entire data file – only those pages with page checksums.

Secondly, a page checksum being valid doesn’t guarantee the page is not corrupt – it just guarantees that the I/O subsystem hasn’t corrupted the page since it was written to disk. A page could have been damaged in memory, and then written to disk with a valid page checksum.

To summarize: using the WITH CHECKSUM option and having the backup complete successfully tells you that those pages with page checksums had not been corrupted by the I/O subsystem at the time the backup was taken.

The only way to know that the database does not have corruptions in it is to run DBCC CHECKDB. I like to advise people to restore the backup on another server and then run DBCC CHECKDB on the restored copy of the database – I’ve blogged about this a few times. If that DBCC CHECKDB comes back clean, you know that the backup contains a corruption-free database.

One thing to bear in mind though is that just because the backup completed successfully and you ascertain that it doesn’t have corruption in, that doesn’t mean that the backup will remain corruption free. An I/O subsystem is just as likely to corrupt a backup file as anything else – which is why regular testing of restore sequence is necessary – especially if backups are moved on and off of tape media.