PostThe Truth About SQL Server Backup Verification Using The GUI

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:

[callout]”RESTORE VERIFYONLY is totally USELESS if you do not have the WITH CHECKSUM option in your backups.”[/callout]

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

[youtube id=”ebn3faTaXU4″]

Additional Resources

[callout]

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


[/callout]

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close