December 31, 2003 at 1:17 pm
I have a number of questions about verifying backups.
First:
When I right click a database, select All Tasks, Backup Database I get a window that lets me select a number of options. I set the device to a fixed drive, path, and file name. Then click schedule, overwrite, and then ok.
Now I repeat the above process and do everything the same except I go to the options tab and select Verify backup upon completion.
Now I copy the code from each of the two jobs created and compare them. They are identical. So how does one verify the backup on completion and the other not if they are using the exact same SQL?
Next question:
Does RESTORE VERIFYONLY do the same type of verification as when you select "Verify the integrity of the backup upon completion" on the Complete Backup tab of a maintenance plan?
What is the best way to verify a backup without restoring it and checking it yourself?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 2, 2004 at 11:00 am
Interesting question. I assume that the Verify backup does just that. Personally we take a slightly different tact.
Each day the backup file is named with the time it starts are part of the filename. We have a job that runs through all folders and scans for the backup file and we have an automated check to be sure it was the one written in the last 24 hours (since we run a full or diff only once a day). Then we also compare this with the size of the previous backup and any substantial differences (> 20%) are flagged for someone to followup. Not prefect, but seems to work well.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
January 2, 2004 at 1:31 pm
The Restore verifyonly is the same that "Verify the integrity of the backup upon completion".
But it only checks that the the backup is readable, not the integrity of the data.
So the best way to really check that the backup is valid, is to restore it. Do it when possible.
January 5, 2004 at 7:34 am
We restore backups about twice a month. We take production backups and restore them in our development environment after each development cycle to make sure it is syncronized with production. We take production backups and restore them to our testing environment to syncronize those databases with production prior to moving new code to testing.
However, we have not had SQL Server ever verify the backups at all and now have a requirement to do so. So the RESTORE VERIFYONLY command should ensure that the backup can be restored.
Hmm, may want to consider comparing sizes with previous backup.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 5, 2004 at 7:01 pm
We've had backups with VERIFYONLY fail to restore correctly with the proper data, so yes, the only sure way is to do the restore. The file size sounds like a great idea. I'll have to pass that along to our production DBAs.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply