Home Forums SQL Server 2005 Backups Testing the Results of RESTORE VERIFYONLY RE: Testing the Results of RESTORE VERIFYONLY

  • Hmm. It won't directly capture the output of RESTORE VERIFYONLY, but doing a SELECT @@ERROR immediately after RESTORE VERIFYONLY will return the count of errors encountered in the RESTORE VERIFYONLY operation; you can do an IF check to insert a message somewhere to notify you of the problem.

    However, one question; why are you doing this sort of testing? If it's for corruption detection, I'd have to advise that RESTORE VERIFYONLY is not suitable for such a thing. RESTORE VERIFYONLY (at least in pre-2012 versions) only scans the header and (maybe?) a bit of data in the backup; in essence, it's just checking to make sure the file you're passing to it is actually a SQL Server .bak file. It won't do a total examination of the backup file, and it will miss corruption in many cases.

    EDIT: Oh, and the MSDN entry for 2012 says that VERIFYONLY has been updated to "be as close to an actual database restore as possible". I haven't read about whether that's truly the case or not, however.

    You are using WITH CHECKSUM in the restore, which will cause a bit more thorough examination, but some corruption can still be missed; I'd suggest reading a bit of this excellent article from Grant Fritchey, in particular, the section that starts with "If I've set the verify flag..."

    Article[/url]

    Personally, I'd go for a full-on restore of your databases as opposed to a VERIFYONLY check, but I understand that space can be limited in many environments. If that's the case, though, I'd make some nudges for more drive space 😀

    - 😀