July 28, 2016 at 8:40 am
All, so far we have had no issues restoring any DB from any backups we have. But just to prevent this, I was thinking about setting up a job which runs and verifies the validity of all backup files I have (RESTORE VERIFYONLY FROM DISK = C:\.BAK). There are about 110+ DBs, weekly full backup, daily dif and hourly tlog backup. What is the best approach for me to accomplish what I am trying to do or if it is even possible?
July 28, 2016 at 8:48 am
Build the verification into your backup job, so it's done as soon as the backup is made.
By the way, VERIFYONLY only checks the file header. You might want to consider using the WITH CHECKSUM option to give you slightly more confidence. But the only way to be totally sure that you'll be able to restore a backup is to test it. You may not want to test all 110 every week - if that's the case, pick some at random and test them.
John
July 28, 2016 at 8:51 am
Thanks for the reply. I appreciate that.
July 28, 2016 at 8:58 am
John Mitchell-245523 (7/28/2016)
Build the verification into your backup job, so it's done as soon as the backup is made.By the way, VERIFYONLY only checks the file header. You might want to consider using the WITH CHECKSUM option to give you slightly more confidence. But the only way to be totally sure that you'll be able to restore a backup is to test it. You may not want to test all 110 every week - if that's the case, pick some at random and test them.
Thomas "SQLRockstar" LaRock did an article for Simple-Talk on Statistical Sampling for Verifying Database Backups[/url] - sounds like it could be useful in this situation.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply