Testing the Results of RESTORE VERIFYONLY

  • Hello All,

    I have a routine that loops through my backup files and runs RESTORE VERIFYONLY on all the backup files. This works fine but I want to use the return value of RESTORE VERIFYONLY to check that the backup file returned a success message. On SSMS console's window the message returned on success is 'The backup set on file 1 is valid.'

    How would I test this with my code? Where do I find this return value in the internals so as to be able to use it in my code? I am using 2008R2.

    Many thanks for your expert help and advice.

    Dave

    IF OBJECT_ID('tempdb..#tempValidateBackup') IS NOT NULL

    BEGIN

    DROP TABLE #tempValidateBackup

    END

    DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName NVARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'D:\Backups\''

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    SELECT name,flag=0 into #tempValidateBackup FROM master.dbo.sysdatabases

    set rowcount 1

    WHILE (exists(SELECT * FROM #tempValidateBackup WHERE flag=0))

    BEGIN

    Select @name=name from #tempValidateBackup WHERE flag=0 and name <> 'tempdb' -- tempdb is excluded as tempdb does not support backup

    SET @fileName = @path + @name + '.BAK'

    RESTORE VERIFYONLY

    FROM DISK = @fileName WITH CHECKSUM

    Update #tempValidateBackup set flag=1 WHERE flag=0

    END

    set rowcount 0

    drop table #tempValidateBackup

    Dave Morris :alien:

    "Measure twice, saw once"

  • 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 😀

    - 😀

  • I would follow hisakimatama's recommendations on the validation of your backups.

    However, to store the value returned by your restore statement, you could use dynamic code and sp_executesql to store the values into a table and fetch them from it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi,

    Thanks a lot for the detailed reply. I totally take your point that a full restore (with DBCC CHECKDB) would be the only way to be absolutely sure that the backups are valid, and you are spot on with the reason why this is an issue here (disk space). I have nudged the powers at be a few times but nothing doing so I'm trying to implement a 'second best' solution, not ideal at all but better than nothing.

    Thanks for pointing out the @@ERROR global variable. I've looked into this as I was not aware of it's use, etc. Doing that digging has led me to a similar approach using TRY and CATCH, and this seems to be working. So thanks for the lead 🙂 I'm inserting the value of @@ERROR into a table and will be using this in my routine.

    I've noticed that the output from @@ERROR is an error code (3013) and not the count of errors as you mentioned. I'm using RESTORE VERIFYONLY within a loop. Does this seem OK to you? I believe @@ERROR is returning the last error code returned in the TRY BEGIN ....TRY END block.

    I've checked out the article you linked to. That looks very useful.

    Once again thanks for the reply.

    Dave

    Dave Morris :alien:

    "Measure twice, saw once"

  • Hi Luis,

    Thanks a lot of for the reply. I'm now storing the results in a table as you suggest. I'm not familiar with dynamic SQL or how to use it with SP_EXECUTESQL, but as you've made me aware of them I will be reading around these soon.

    Many thanks,

    Dave.

    Dave Morris :alien:

    "Measure twice, saw once"

  • seatedElephant (1/14/2014)


    Hi,

    Thanks a lot for the detailed reply. I totally take your point that a full restore (with DBCC CHECKDB) would be the only way to be absolutely sure that the backups are valid, and you are spot on with the reason why this is an issue here (disk space). I have nudged the powers at be a few times but nothing doing so I'm trying to implement a 'second best' solution, not ideal at all but better than nothing.

    Thanks for pointing out the @@ERROR global variable. I've looked into this as I was not aware of it's use, etc. Doing that digging has led me to a similar approach using TRY and CATCH, and this seems to be working. So thanks for the lead 🙂 I'm inserting the value of @@ERROR into a table and will be using this in my routine.

    I've noticed that the output from @@ERROR is an error code (3013) and not the count of errors as you mentioned. I'm using RESTORE VERIFYONLY within a loop. Does this seem OK to you? I believe @@ERROR is returning the last error code returned in the TRY BEGIN ....TRY END block.

    I've checked out the article you linked to. That looks very useful.

    Once again thanks for the reply.

    Dave

    Haha, I've got the same problem where I work :-). Not enough disk space to restore all of these databases and check everything, nor do I have a provisioned server to restore on. I've made the suggestions, but... Who knows when/if they'll be acknowledged. Common enough problem, sadly! At the least, though, a CHECKDB of each database routinely would be highly recommended (if not already implemented), along with your restore process, to minimize the probability of overlooking something.

    Yep, the results of doing a SELECT @@ERROR will return the error code encountered in the last T-SQL statement; if no errors were encountered, it returns 0. Therefore, a quick check of something like IF @@ERROR > 0 will activate if any errors were encountered in your last statement (to the best of my knowledge). I clearly wasn't running on coffee when I said it was the count of errors earlier :w00t:. Checking the value of @@ERROR after each attempted restore should pick up any errors in the process.

    - 😀

  • Hi,

    Good to hear it's not just me who has to get by on a shoestring. I like to think it makes us more creative 😀

    I've got DDCC CHECKDB running as well, which as you say does give me some reassurance. I still would love to be able to to a full restore regularly. Maybe if I harp on about it enough they'll give in and I'll get some disk space.

    Thanks for clarifying on the @@ERROR, this is new to me and has come in very handy.

    Cheers,

    Dave.

    Dave Morris :alien:

    "Measure twice, saw once"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply