What are the challenges you face when working across database platforms? Take the survey

How can I capture SQL Server Error thru SQL Backup SP?

essamughalessamughal Posts: 35
edited January 20, 2006 12:17PM in SQL Backup Previous Versions
Hi ;

I am trying to capture SQL Server Error raised thru the SQL Backup Extended SP. What I found It retures 1 in case of successful restore and if there is any error then it returns 0, which is fine. I am doing that with the following code.

Declare @L_ErrorCode int

exec @L_ErrorCode = master..sqlbackup '-SQL "RESTORE LOG [pubs] FROM DISK = ''E:\Dump\LOG*.sqb'' WITH
MOVETO = ''E:\Dump\Processed\'',
STANDBY = ''E:\Program Files\Microsoft SQL Server\MSSQL$SECONDARYSITE\BACKUP\UNDO_pubs.DAT'',
LOGTO = ''E:\Dump\Processed\SQBSecondaryLog.txt'' ,

SELECT @L_ErrorCode

What I want to do, I want to capture SQL Server Code or Exit Code of the API.

Example One:
For Example if there is no file it thru that Exit Code 220 but sqlerrorcode 0 b/c it is not an sqlerror :

SQL Backup (DLL v4.0.0.113)
Warning 220: No log files found to be restored.

SQL Backup exit code: 220

name Value
exitcode 220
sqlerrorcode 0
filename01 E:\Dump\LOG*.sqb

Example Two:

Another Example If the sequence of the file is changed then it thru the following error In that ExitCode is 0 but sqlErrorCode is 4305:

Server: Msg 3013
RESTORE LOG is terminating abnormally.
Server: Msg 4305
The log in this backup set begins at LSN 24009000001520300001, which is too late to apply to the database. An earlier log
backup that includes LSN 24009000001519800001 can be restored.

exitcode 0
sqlerrorcode 4305

My Question is that:
I need to capture SQL ERROR Code in a variable to raise an error from by SQL Server Job so that I can report mark the job as failed? Is there any output parameter which I can capture in a variable? :?:

B/c SQL Backup API always sends an Email with the heading SUCCESS - Restore but the body of the email says that the restore is terminated abnormally b/c of the wrong LSN.



Sign In or Register to comment.