How can I capture SQL Server Error thru SQL Backup SP?
essamughal
Posts: 35
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
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
Comments
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
It is cool.
Thanks