How can I capture SQL Server Error thru SQL Backup SP?
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'
What I want to do, I want to capture SQL Server Code or Exit Code of the API.
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 v18.104.22.168)
Warning 220: No log files found to be restored.
SQL Backup exit code: 220
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.
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.