Reporting Backup Errors to SQL Agent

RGnk · December 14, 2007 4:21AM

Hi,

We are encountering backup errors with v 5.1.0.2781. The error message we receive is "Warning 210: Error writing to backup file \\\RemoteServer\Directory\File.sqb System error (The specified network name is no longer available)...Process terminated unexpectedly. Error code -2139684860... SQL error 3013: BACKUP DATABASE is terminating abnormally."

However, the backup is being run via a SQL Agent Job (2000 sp4) and the agent is reporting success. This prevents me from retrying the backup. I am only seeing the error in the redgate GUI.
Can anyone suggest why I am failing to pass the error event back to the SQL Agent? The script I am using is as follows:

DECLARE @exitcode int
DECLARE @sqlerrorcode int

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [MyDb] TO DISK = ''\\RemoveServer\RemoteDirectory\MyDb.sqb''
WITH VERIFY, COMPRESSION = 2, INIT"'

IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

Any help greatly appreciated.

petey · December 16, 2007 9:54PM

When SQL Backup fails to write data out to a file, it raises warning code 210. This is escalated to error code 620 if all files fail to be written to.

In your case, it appears that error 620 is not being raised, which is why the SQL Backup Agents failed to recognise the error, since it is set up to only captures error codes (>= 500). Warning codes have values less than 500.

Could you please post the contents of the SQL Backup log for this backup? The default folder for logs is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>. Thanks.

RGnk · December 17, 2007 5:17AM

Thanks for your reply. Below is the log file (names changed to protect the innocent):

SQL Backup log file
14/12/2007 02:00:08: Backing up Mydb (full database) to:
14/12/2007 02:00:08: \\RemoteServer\backup\sql backups\MyDb\MyDbFull.sqb

14/12/2007 02:00:08: BACKUP DATABASE [MyDb] TO DISK = '\\RemoteServer\backup\sql backups\MyDb\MyDbFull.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, VERIFY, COMPRESSION = 2

14/12/2007 03:06:25: Thread 0 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860
14/12/2007 03:06:25: Warning 210: Thread 0 warning:
Warning 210: Error writing to backup file: \\RemoteServer\backup\sql backups\MyDb\MyDbFull.sqb
Warning: System error (The specified network name is no longer available)

SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
SQL error 3202: SQL error 3202: Write on 'SQLBACKUP_EB06F3CF-150D-4F98-A8A7-C831F3E5B755' failed, status = 112. See the SQL Server error log for more details.

chris.buckingham · December 17, 2007 6:58AM

In addition you have to edit your EXECUTE command line by adding the @exitcode and @sqlerrorcode as follows:

DECLARE @exitcode int
DECLARE @sqlerrorcode int

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [MyDb] TO DISK = ''\\RemoveServer\RemoteDirectory\MyDb.sqb''
WITH VERIFY, COMPRESSION = 2, INIT, @exitcode OUTPUT, @sqlerrorcode OUTPUT"'

IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

petey · December 17, 2007 9:45PM

Chris is right. Your original script is missing the two output parameters that SQL Backup can use to return the process values.

Reporting Backup Errors to SQL Agent

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk