February 3, 2008 at 6:59 pm
I'm curious as to what others do regarding verifying the backup file. One of our largest DB's is about 280GB's. Our backup usually works fine, but we've run into problems with the restore verifyonly taking very long to run or will fail somewhere in the middle. So I'm unable to determine if the file is valid.
What do you typically do for an environment of this size?
Thanks
J.D.
February 3, 2008 at 7:57 pm
I've run a restore a week with something this large and crossed my fingers it was working the other days. The issue is that often you might not even know if it's good until you restore it, at which time you might be back to the next backup.
I'd run the backups and schedule regular restores. Be sure your backup medium is well protected with RAID as well.
February 3, 2008 at 8:07 pm
Here's a suggestion: copy the backup files to a redundant box, and run a RESTORE VERIFYONLY on those files using SQL Server 2005 Express Edition. SSEE actually allows you to run a RESTORE VERIFYONLY on files larger than 4 GB, so that's a very cost-effective way of verifying your backups. The only requirement is to have enough storage space for the files.
You might also consider using the CHECKSUM option when performing the backup to gain a further degree of assurance, though that will have an impact on backup time, the margin of which will depend on your server configuration and utilisation.
Finally, you might also want to consider the use of 3rd party tools to speed up backup, restore and verification processes.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 3, 2008 at 9:59 pm
I'll echo Steve here - the only true/provable test of a backup is to restore it periodically - I can't count the number of times that I've found backups to have unexpected problems. Not so long ago a client was backing up their "key" CRM/client database to tape every night via a well known backup agent - when we attempted to restore the database from the most recent full backup (e.g. request tape from offsite storage, restore) we found that the tape contained a copy of the database from six months ago...
Joe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply