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

essamughal

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'' ,
PASSWORD = ''12345''" -E -I SECONDARYSITE'

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.

Thanks
Essa

petey

The extended stored procedure accepts two additional output parameters, for the exit code and the sql error code. E.g. of use

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup '-sql "BACKUP ..."', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <> 0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup step failed: exit code %d, SQL error code: %d',
16, 1, @exitcode, @sqlerrorcode)
END

You can find more details from the help file (http://www.red-gate.com/support/help_files/sqlbackup.chm), under the Reporting SQL Backup job failures via SQL Server Agent topic.

essamughal

Thanks for that, now I can see the job is failed if there is an error during restore.

It is cool.

Thanks