April 24, 2008 at 7:11 pm
I am an experience developer, but am just getting familiar with SQL Server 2005 and TSQL and am stuck on how to check the results of a TQSL statement and suppress SQL Agent's reaction to certain errors? Here is an example:
begin
RESTORE( LOG [MYDATA] FROM DISK = @trnfile
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
end
How can I have the script trap and obtain the results of the RESTORE statement?
How can I have the script avoid having SQL Agent raise an error if the failure is not one I am concerned about (e.g. file being too early), yet raise one if the failure is one I care about (e.g. a gap in the log chain).
April 24, 2008 at 8:50 pm
Have you done a try..catch around this? Not sure if it will help. RESTORE might be one of those functions where you can't necessarily trap the error.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 25, 2008 at 10:51 am
That did the trick. Thanks!
Being a TSQL rookie, I had not yet learned of Try...Catch, but having done a little C#, I should have thought to look for that. RTFM! (or at least an intro book)
May 5, 2008 at 5:17 pm
Can you tell me how to handle this situation. There are two errors listed when the log backups is too old:
Msg 4305, Level 16, State 1, Procedure uspRestoreLogs, Line 194
The log in this backup set begins at LSN 55458000000187900001, which is too recent to apply to the database. An earlier log backup that includes LSN 55339000000055100001 can be restored.
Msg 3013, Level 16, State 1, Procedure uspRestoreLogs, Line 194
RESTORE LOG is terminating abnormally.
In the BEGIN CATCH section, Error_Number() returns 3013, the generic message that tells me very little. How can I detect that the actual error was 4305? (I want to ignore this error, but re-raise any others).
May 5, 2008 at 5:20 pm
Oops! The example showed the wrong error (one I want to re-riase), but it still illustrates the question ... how can I detect what was behind the 3013 error?
May 5, 2008 at 8:54 pm
try this link..might help
May 6, 2008 at 1:03 pm
I don't think you understood my question . Here it is again:
How can I detect the 4xxx error message in the "catch" portion of TSQL script when an in-line RESTORE LOG operation fails due to a 4xxx error?
I cannot use Error_Number() and @@Error because they return 3013 (the second error), which tells me that the restoration was terminated, but not why the restoration was terminated.
Is there any other way to get the 4xxx error?
For example, is it possible to access the job's log while the job is running? If so, then I could look for the 3013 then scan backwards to check for a 4xxx errror.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply