The Truth About SQL Server Backup Verification Using The GUI

Why You Shouldn't Just Rely On The Checkbox That Says So

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

IF YOU ENJOYED THIS POST, GET MORE UPDATES STRAIGHT TO YOUR INBOX.


     

Comments

comments

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *