Corruption and Verifying Backups

This morning I had a conversation with a Subject Matter Expert and Application Administrator who asked me quite simply for a verified backup before he does an install on Saturday.   My response was that I could test it manually for them and I was willing to do so but currently with our infrastructure the nightly backups are not tested without manual intervention.  I have tried to change this policy for some time and unfortunately it may not occur until something bad happens.  With this in mind, I do the best I can to minimize the risk given that I cannot automate a solution to restore databases on a regular basis as I would prefer.  How can you minimize the risk?

  1. Use the WITH CHECKSUM option on your backup scripts.  Read Paul Randall’s The Importance of Validating Backups article on SQL Server Central for more information.
  2. Use the RESTORE VERIFYONLY command (although we use Ola Hallengren’s Backup Solution which is the verify option which runs the command).
  3. Use the WITH CHECKSUM on the RESTORE command to recheck the page checksums in the backup as well as the backup file.
  4. Automate or manually restore your databases from scratch on a regular basis.  (This is where we are lacking and where I want to take my servers).
  5. Once you have restored, then run DBCC CHECKDB against the restored database.

If you are not doing all five then you cannot say confidently that your backups are verified.  However, if you are doing all five keep in mind that there is no fool proof guarantee against corruption, this merely minimizes the destruction by having viable backups.  I hope this helps….Enjoy!

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on November 26, 2012, in Maintenance, Scripting and tagged , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: