I recently had a conversation with a SQL Server DBA about making sure that backups do work as expected. And while I try not to use scary tactics to convince people to change their way of thinking, this particular example needed it.

I’ve written a blog post about why I don’t trust the “Verify backup integrity” option in the SQL Server Management Studio database maintenance plan. The main idea behind the blog post is that you may have believed this to be true for a long time but it actually isn’t: it does not verify backup integrity because it does not perform integrity checks.

I’ve provided a set of scripts in the blog post to use as an example. I took it a step further – I’ve recorded a video to drive the point further. If I was to summarize the lesson in a single sentence, it would be this:

“RESTORE VERIFYONLY is totally USELESS if you do not have the WITH CHECKSUM option in your backups.”

A recommended approach is to NOT use the database maintenance plan backup database tasks to create and verify your backups. Rather, use T-SQL and include the appropriate keywords – like the CHECKSUM for backup and VERIFYONLY for restore.  Have fun learning

Additional Resources

• The Scariest Lie We Believed About Backup Verifications
• Is a Successful Backup Really a Successful Backup?
• Are Your SQL Server Backups Useless?