The SQL Server backup shows success…but is it successful?

IT professionals often make backups of their SQL databases using either native SQL maintenance plans or custom scripts. Others leverage third-party tools, which also work well. And when the backup indicates it’s “successful,” they believe all is well. But is it really?
Our aim here is to help you avoid avoiding a potentially false sense of security by relying solely on the “success” or “failure” of a backup job. One customer quickly discovered his database restore was failing due to corrupted pages. The customer had a couple of months’ worth of backups, but guess what? The corruption ran deep!
So, let’s take a step back at what happened and see why the backup job wasn’t reporting any failures, despite the corrupted files. When we run the BACKUP DATABASE command, typically we see something like this:

Here we’re simply backing up a DBATools database to a network share. When this backup is successful, the following message appears:

Looking at this, one would assume the backup was successful. But was it? Or would the outcome still display “success” if there was corruption in the backup set? The short answer is you betcha!
So how can we validate the success of our backup, really?
The key is to use the CHECKSUM option in your BACKUP DATABASE command, like so:

A quick word on CHECKSUM. This option will test page checksums on the data file pages being backed up. If a bad page checksum is found, the backup process will stop by default (we could override this behavior, but that’s a discussion for another day). So, if there is any corruption in the database (or bad checksums), a useable backup will not be generated until you fix the corruption.
In my next blog post, I’ll discuss Trace Flag 3023 (enables CHECKSUM on all database backups) which is a Ron Popeil, “set it and forget it,” solution. I’ll also provide additional recommendations for finding the true sense of security (and serenity) that comes with achieving solid, reliable backups for your enterprise.
If your organization would like help finding the ideal solution to ensure reliable backups every time, please don’t hesitate to reach out. We’d love to get you started.