Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2013-04-24 : 12:40:00
|
| 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!CheersMIK |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-24 : 12:54:02
|
| 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/ |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-04-24 : 13:12:08
|
| 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. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-24 : 13:37:10
|
| 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?CheersMIK |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-24 : 14:41:32
|
| 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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-25 : 09:42:23
|
| Another resource: https://www2.gotomeeting.com/register/753873682 |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-04-25 : 14:29:27
|
| 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. |
|
|
|
|
|
|
|
|
Restore VerifyOnly