How to I trap the first error in a process that has failed (Only using restore DB snapshot as an example)

• pjl0808

  SSC Eights!

  Points: 834

  More actions

  April 20, 2012 at 9:18 am

  #254523

  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

  3013 16 1 NULL 4 RESTORE DATABASE is terminating abnormally.

  Not worried about the file missing, I just want to know how to I trap the first error? Could be anything throwing 2 errors.

  Thanks

  P

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  April 20, 2012 at 11:32 am

  #1476842

  Please do not cross-post. It only fragments replies and wastes people's time. Direct all replies here:

  http://www.sqlservercentral.com/Forums/Topic1287003-146-1.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato