January 10, 2014 at 6:10 am
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"
January 10, 2014 at 10:01 am
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..."
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 😀
- 😀
January 10, 2014 at 10:29 am
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.
January 14, 2014 at 2:51 am
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"
January 14, 2014 at 2:58 am
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"
January 14, 2014 at 7:44 am
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.
- 😀
January 15, 2014 at 7:30 am
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