Reporting Backup Errors to SQL Agent

• 
  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.

  petey December 16, 2007 21:54

  - 0 +
• 
  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.

  RGnk December 17, 2007 05:17

  - 0 +
• 
  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 December 17, 2007 06:58

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

  petey December 17, 2007 21:45

  - 0 +

Add comment

Please sign in to leave a comment.