SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restore VerifyOnly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/24/2013 :  12:40:00  Show Profile  Reply with Quote
Hello folks, a quick question.

A concept goes around that once a backup of a database is taken then its a good practice to verify it via "Restore VerifyOnly ...".

My question is Why? If I've taken a backup of databases and its completed successfully then why do I need to verify it? On the other hand, if its not successful then I would not be having even a back and so nothing to verify. Thank you!

Cheers
MIK

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 04/24/2013 :  12:54:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
Actually your best practice is to restore of all your backups to a secondary server. It's possible for disk corruption to occur during or after a backup that will prevent a successful restore. VERIFYONLY has improved from earlier versions (which did not check for restorability) but it's still better to restore the backup and make sure it's successful. A backup that can't be restored is worthless, the same as no backup at all.

There's lots of excellent advice on backup and restore strategy here: http://www.sqlskills.com/blogs/paul/category/backuprestore/
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
769 Posts

Posted - 04/24/2013 :  13:12:08  Show Profile  Reply with Quote
The only way to truly verify a database backup is to restore the database from that backup. RESTORE VERIFYONLY does not actually verify the ability to restore the backup - but does verify that the backup file can be read completely.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/24/2013 :  13:37:10  Show Profile  Reply with Quote
Thanks Rob and Jeff, I agree about with the best practive of moving backups to a secondary server ....

However I was just wondering to know the need of introducting "Restore VerifyOnly" concept by the Microsoft. There might be a reason but I am not aware of it. What I think is that there is no use of verifying a Backup, since

1) if a database is backed up successfully (e.g. backup database test to disk='X:\test.bak' returns message: BACKUP DATABASE successfully processed) then it should be restored successfully as well.
2) If backup doesn't get successful due to any reason, an error message, and thus no backup file at all.

In otherwords, when and why do I need to use the Restore VerifyOnly?

Cheers
MIK
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 04/24/2013 :  14:41:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can look at VERIFYONLY as a holdover from the Sybase days and just ignore it, and do full restore testing instead.

#1 is absolutely not true, you cannot assume a successful backup can be successfully restored. Disks fail; one sector of a file could be corrupted after the backup is done, rendering the backup file useless, etc. The only way to guarantee the restore will succeed is to restore it.

I cannot stress enough that you should read Paul Randal's blog articles on backup/restore, he describes numerous scenarios where corruption can occur and how it can impact restorability.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 04/25/2013 :  09:42:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
Another resource: https://www2.gotomeeting.com/register/753873682
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
769 Posts

Posted - 04/25/2013 :  14:29:27  Show Profile  Reply with Quote
If you cannot perform an actual restore due to resource constraints (e.g. no other server/storage to restore to), and you have the time available on the actual server - then using RESTORE VERIFYONLY gives you at least some idea whether or not the backup file is valid.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000