asked
Backup Validation
This might be better suited for the forum, but I like the Q/A better. Here goes... Most of us on here support production databases and we all have to make various changes to databases over the course of our job. Some things are simple enough where you run an update script to update a few records, no big deal. Then we have those changes that are pretty extensive where we need to make a full backup as a back out plan. When you make that full backup, what do you do if anything to validate it is good? Has anyone encountered issues before where you take a full backup, it didn't generate any errors, your update script blows up and you have to recover only to find out your most recent backup was junk? I am fortunate that this hasn't happened to me yet and hopefully wont. :) Do you do a quick RESTORE WITH VERIFY ONLY or do you take the time to do a full RESTORE to a different name, or do you just do a full BACKUP and hope for the best. If we are all being honest I have to confess that I typically just do a full backup and go with it. I have had to restore many times after a vendor supplied script blew up and haven't had issues, but as I am trying to mature a bit in my profession I am thinking I should take some extra steps to validate. So who is up for suggestions and explanations.
At my previous employer we had every mission critical database going through a daily restore in addition to a verify on the backup and DBCC checks prior to the backup (we had slow periods at night so we could do this). But you're talking about production deployments. Those, no. We didn't restore them prior to going forward. One thing we did do though, as apps moved to 2008 or R2, we would use snapshot backups instead of full backups. They're faster, and frankly, safer, than trying to run a full backup and then a full restore after you mangle something in production (not that I would ever mangle anything, fingers crossed & whistling as I examine the paint on the ceiling). Depending on your level of paranoia and the amount of time you have to work with, should you test them? Yes. I'd be remiss if I didn't point out that you can use [Red Gate SQL Virtual Restore][1] to do a quicker backup validation without using up disk space than if you just did a normal restore operation. [1]:
http://www.red-gate.com/products/dba/sql-virtual-restore/entrypage/painkillers
We restore all of our database backups every day. Because restore failures from this process are so few and far between, we are comfortable without doing an additional restore of the special pre-roll out backup prior to the roll out. In addition to the additional full backup prior to a production rollout, we also take a database snapshot so we have a super quick way to revert our changes that is independent of the backup itself. We have not been bitten by a bad backup on a production roll out yet.
As Jorge Segarra aptly put it in his [Back That Thang Up][1] presentation:
Why backups are worthless? Because restores are priceless!There are links to Paul Randal's blogs in his presentation. One link is to the [various myths around backups][2] (look for 30-11) and another to the [importance of validating backups][3]. [1]: http://dl.dropbox.com/u/1960017/Presentations/Back%20That%20Thang%20Up.pptx [2]: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx [3]: http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-validating-backups.aspx
Thanks @KenJ. We also have a routine that does full restores in a different environment for backup validations of other systems. We don't restore every database every day as we support close to 3000 databases and 80TB of data. We randomize it so that every database is validated at least once per week. I like the snapshot idea before a production rollout.
If you follow master Yoda's advice (aka Paul Randal) he would say that the RESTORE command with VERIFYONLY and CHECKSUM can actually check the integrity of your backup. Which just short of restoring it should give you a good picture if the backup is good. However the catch is that the backup you are checking has to have used the CHECKSUM option (in the BACKUP command) when it was taken, otherwise you cannot use CHECKSUM with the RESTORE VERIFYONLY command to check it. You can read this in the [SQL Server Magazine article][1] he wrote on June 1, 2011 [1]:
http://www.sqlmag.com/article/database-backup-and-recovery/advanced-backup-and-restore-options-129834
