April 20, 2012 at 4:38 am
Hi All,
I'm testing a stored proc to restore a database snapshot and not able to trap the first error when the snapshot is missing.
RESTORE DATABASE Pjl_Test FROM DATABASE_SNAPSHOT = 'Pjl_Test_SS'
returns the following 2 errors:
Msg 911, Level 16, State 4, Line 1
Could not locate entry in sysdatabases for database 'Pjl_Test_SS'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Using:
BEGIN TRY
RESTORE DATABASE Pjl_Test FROM DATABASE_SNAPSHOT = 'Pjl_Test_SS'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Traps the 2nd error from above:
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
3013161NULL4 RESTORE DATABASE is terminating abnormally.
How to I trap the first error?
Thanks
P
April 20, 2012 at 9:35 am
Unfortunately I do not think you can unless you execute the command from something like PowerShell or C# where there is an Exception Collection available. In T-SQL you will only see the last error raised.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 6:01 am
Why not do a bit of code before the restore in an IF EXISTS type of statement?
All you have to do is search the sys.databases table where source_database_id IS NOT NULL and it has the name of your database. If the snapshot exists, it'll be there. If it doesn't exist, then it won't be there.
Then you can restore between the BEGIN / END parts and have an ELSE statement that sends a "there was no snapshot" message, or vice versa.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply