| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-10-22 : 16:48:24
|
| Hi thereI need some feedback regarding the option in Maintenance Plan which is "Verify the integrity of backup upon completion".This option takes the same amount of backup. Sometimes 2 or 3 hours. Is this option necessary? BTW ... we don't have Automatic Test Backup procedure in our envronment as yet ... we do occasionally but not everyday.I am appreciated your comment.Thanks |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-22 : 16:53:50
|
| the option, imho, is completely unneccesary and a waste of time. The only real way to verify a backup, is to actually restore it.-ec |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-22 : 17:17:45
|
| Thanks EyeChart.I might to consider this but at the meantime while the "verification running" is there any way to find how long to go or what is being done?Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-22 : 17:22:32
|
| No, there's only feedback during the backup phase, but as you noticed, the verification process takes as long as the backup does.There is an enhancement for SQL 2005, either current or to be provided in a service pack, where a verification will actually ensure the backup is valid and restorable. |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-22 : 20:15:08
|
| Are all dba's kinda agree that the "Verify the integrity of backup upon completion" is completely unneccesary and a waste of time? |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-10-23 : 06:55:52
|
| Well I'm with eyechart on this one . . the only way to verify a backup is to restore it . . . And don't ask me how I know this :(-- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-23 : 14:41:21
|
| Restore it to a different server, and use DBCC CHECKDB('MyDatabase') NO_INFOMSGSon the other server to test that the Restore was OKEdit: Fixed typo - wrong sort of bracket!Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-10-23 : 20:47:00
|
| +1 to the Restore and testKristen's CHECKDB example is a good one.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2006-10-24 : 04:17:38
|
| GurusI dont fully agree with this.I was in a situation few days ago where i was passed on a backup file and was told to restore it urgently.and then i checked the integrity of the backup file with restore verifyonly command and i told the client straight away that the backup set is corrupt.I know i could have come to know about this at the time of restoring the Database but i think checking if the backup set is valid or not does helpRegardsNitin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-24 : 05:41:25
|
| "i think checking if the backup set is valid or not does help"If it tells you the file is corrupt it helps, but my understanding (SQL2000) is that "File is OK" doesn't tell you that DBCC CHECKDB won't then say "You're hosed!"But even so its a good point - restoring a 10GB file and running DBCC is a bit of a waste of time if RESTORE VERIFYONLY could have told you the file was bust! I suppose I work on the basis of a file very rarely being bad, and thus getting it upright as quickly as possible is the aim (and I still have to do the DBCC CHECKDB anyway)Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-24 : 11:40:37
|
| RESTORE VERIFYONLY only checks if the file is readable by SQL Server. No actual verification occurs - at least not in SQL 2K.If you want to spend the time verifying your backups in this way, go right ahead. But keep in mind that you are not verifying anything with this and this is absolutely not the way to validate your backups.-ec |
|
|
|
|
Maintenance Plan: Verify the integrity of backup