How to tell when a 'RESTORE VERIFYONLY' statement is executed

Question

Post reply

How to tell when a 'RESTORE VERIFYONLY' statement is executed

tiffanyjanetblack

SSCommitted

Points: 1877
More actions
November 2, 2010 at 1:45 pm

#240711

Using a 3rd party app with SQL 2005 back end.
App calls a stored proc that executes 2 dynamic sql statements.
1st stst runs a backup....and I needed to know start and end time of backup, which I found in msdb.backupset table.
SELECT @SQLCommand = 'BACKUP DATABASE ' + @dbname + ' TO DISK = N''' + @filelocation + @filename + ''' WITH RETAINDAYS = 8, NOFORMAT, INIT, NAME = N'''+ @dbname + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
EXECUTE ( @SQLCommand)
2nd stmt issues a RESTORE VERIFYONLY command to verify the backup just created.
Cannot find anywhere the start and end time of the verify command. desperately need this info.
SELECT @SQLCommand = 'RESTORE VERIFYONLY FROM DISK = N''' + @filelocation + @filename + ''' WITH FILE = ' + convert(char(1),@backupSetId) +' , NOUNLOAD, NOREWIND'
EXECUTE ( @SQLCommand)
Is this information to be found? Or is it not logged as it is not technically an event?
Have spent 3 days on this as am trying to prove to vendor that the issue is not on the SQL side and is in fact in their source. Has been causing severe delays in very critical business processes.
Any thoughts?

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I don't think you'll find that anywhere. Why don't you just put some data logic into the SP around the VERIFY command?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Adiga One Orange Chip Points: 27284 More actions · Answer 2

The output of RESTORE VERIFYONLY is not logged in the SQL Server Error Log. If you are executing the custom stored procedure through a job, the job step output file will have the information about this. Also try checking the job history.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

tiffanyjanetblack SSCommitted Points: 1877 More actions · Answer 3

Well, it isnt run from a SQL Agent job. Hence the problem.

Oh well...

tiffanyjanetblack SSCommitted Points: 1877 More actions · Answer 4

We did. I put a little dbmail hack in between the steps so I could get the time each item began and ended.

I reckon that's the best I am going to get.

thx!

John Rowan SSC Guru Points: 56440 More actions · Answer 5

You should be able to capture the events via SQL Profiler.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

tiffanyjanetblack SSCommitted Points: 1877 More actions · Answer 6

tiffanyjanetblack

SSCommitted

Points: 1877

November 3, 2010 at 11:05 am

#1246173

I should have thought of that.

Will try.

Thanks!!!