Reporting Backup Errors to SQL Agent

RGnkRGnk Posts: 3
edited December 17, 2007 9:45PM in SQL Backup Previous Versions
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.

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • 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
    Chris Buckingham
    Red-Gate support
  • peteypetey Posts: 2,358 New member
    Chris is right. Your original script is missing the two output parameters that SQL Backup can use to return the process values.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.