Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Testing the Results of RESTORE VERIFYONLY Expand / Collapse
Author
Message
Posted Friday, January 10, 2014 6:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:18 AM
Points: 88, Visits: 323
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

"Measure twice, saw once"
Post #1529749
Posted Friday, January 10, 2014 10:01 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 608, Visits: 6,675
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

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




-
Post #1529880
Posted Friday, January 10, 2014 10:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 3,667, Visits: 8,006
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529898
Posted Tuesday, January 14, 2014 2:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:18 AM
Points: 88, Visits: 323
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

"Measure twice, saw once"
Post #1530580
Posted Tuesday, January 14, 2014 2:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:18 AM
Points: 88, Visits: 323
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

"Measure twice, saw once"
Post #1530581
Posted Tuesday, January 14, 2014 7:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 608, Visits: 6,675
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 . Checking the value of @@ERROR after each attempted restore should pick up any errors in the process.




-
Post #1530688
Posted Wednesday, January 15, 2014 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:18 AM
Points: 88, Visits: 323
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

"Measure twice, saw once"
Post #1531099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse