January 31, 2011 at 4:47 pm
i am running the below query and want to capture the validity message:
restore verifyonly from disk='D:\adventureworks.bak'
The backup set on file 1 is valid.
Can anyone let me know how to capture the message in a table
January 31, 2011 at 4:55 pm
AFAIK, you can't capture the stuff on the information table in a table. That is an out of band message sent to the client, and I don't know of a way to consume that in T-SQL.
January 31, 2011 at 7:39 pm
Just as a wild thought - you could use something like this:
BEGIN TRY
EXECUTE ('restore verifyonly from disk=''C:orthwind.bak''')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER()AS ErrorNum,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
INTO #OOPS
END CATCH;
SELECT * FROM #OOPS
--DROP TABLE #OOPS
It will capture errors in the temporary table #OOPS. But that could be changed to an insert into a permanent table. And in similiar fashion insert some data into that same permanent table in the TRY BLOCK
As I said a wild thought to stir your mind to eventually doing what you desire to do.
January 31, 2011 at 9:32 pm
It doesn't work for me..temp table #OOPS is not getting created.
February 1, 2011 at 6:37 am
sunder.bugatha (1/31/2011)
It doesn't work for me..temp table #OOPS is not getting created.
The original code I posted, if it completed successfully would not have created the temp table #OOPS. Now I have expanded the code to use a permanent table, and to write to that table upon either success or failure.
CREATE TABLE Restor(ErrorNum INT,Severit INT,E_State INT,PRO VARCHAR(50),ErrorLine INT, Mess VARCHAR(100),
[Asof] [datetime] NULL CONSTRAINT [DF_Restor_Asof] DEFAULT (getdate()))
BEGIN TRY
EXECUTE ('restore verifyonly from disk=''C:orthwind.bak''')
INSERT INTO Restor(Mess,AsOf)
VALUES ('Successful',GETDATE())
END TRY
BEGIN CATCH
INSERT INTO Restor
SELECT
ERROR_NUMBER()AS ErrorNum,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
GETDATE()
END CATCH;
SELECT * FROM Restor
September 30, 2014 at 1:24 pm
Ther above code will NOT return the Message that is printed to the Message Tab. The minute you use try - Catch you lose that.
I too am looking for a way to get the message that is actually returned as it provides much more info.
Following TSQL:
RESTORE VERIFYONLY FROM DISK = 'F:\Temp\SkDataDemo-20140929203m7.bak'
If I deliberately use a bad file name for example I get the following to the screen:
Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'F:\Temp\SkDataDemo-20140929203m7.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 3
VERIFY DATABASE is terminating abnormally.
If I wrapper the above line in a TRY-CATCH all I get back is:
VERIFY DATABASE is terminating abnormally.
I would REALLY like to capture the full message.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply