SQL Backup Completes with Success but Backup Failed

• 
  Does the SQL Server Agent job check for the exit code and SQL error codes upon completion of the backup?

  petey February 08, 2006 18:39

  - 0 +
• 
  No. Here is the stored procedure I'm running though:
  

  CREATE  procedure dbo.backupSchemasSQLBackup 
  as
  	declare @schemaName varchar(50)
  	declare @sql varchar(4000)
  
  	/* Make sure database is online (status & 512) = 0 before trying to back it up. */
  	declare schemaList cursor read_only for
  		select [name] 
  		from master..sysdatabases
  		where [name] not in ('tempdb')
                                       and (status & 512) = 0
  		order by [name]
  
  	open schemaList
  	fetch next from schemaList into @schemaName	
  	while ( @@fetch_status = 0 ) begin
  		-- Delete Old Database Backup
  		set @sql = 'exec xp_cmdshell  ''del  L:\SQL_Backups\' + @schemaname + '.sqb'''
  		print @sql
  		exec ( @sql )
  
  		-- Backup Database to New SQL Backup File (the one just deleted)
  		set @sql =  'master..sqlbackup ''-SQL "BACKUP DATABASE [' + @schemaName + ']  TO DISK = ''''L:\SQL_Backups\' + @schemaName 
  		   + '.sqb'''' WITH NAME = ''''Database (' + @schemaName + '), ' 
  		   + replace(replace(replace(convert(varchar(19),getdate(),121),'-',''),':',''),' ','_') 
  		   + ''''', DESCRIPTION = ''''Backup on ' + convert(varchar(19),getdate(),121) + ' Database: ' + @schemaName 
  		   + ' Server: NJCINNT37'''', INIT, MAILTO = ''''abuttery@impactrx.com'''', COMPRESSION = 1"'''
  		print @sql
  		exec ( @sql )
  
  		-- Copy ZIP File to NT34
  		set @sql = 'exec xp_cmdshell "copy L:\SQL_Backups\'  + @schemaname + '.sqb ' + '\\NJCINNT34\f$\NT37\' + @schemaname + '.sqb", no_output'
  		print @sql
  		exec ( @sql )
  
  		print '----------------------------------------------------------------------'
  		fetch next from schemaList into @schemaName
  	end
  	close schemaList
  	deallocate schemaList
  GO
  

  
  The backups are being run on NJCINNT37 and then a copy of the backed up file is saved on NJCINNT34 for quick disaster recovery.
  
  The basic question still remains, why is SQL Backup indicating that the backup was successful when it cannot even write to the output device?
  
  -- Alex Buttery

  abuttery February 08, 2006 20:07

  - 0 +
• 
  It is SQL Server Agent that is reporting the successful completion of the job.
  
  SQL Server Agent reports a failure only if it encounters an error when it executes a T-SQL task. By all means, the SQL stmt that you are running is valid and runs perfectly. However, the result from that T-SQL task is a failure code, which SQL Server Agent does not catch (unlike a command line job step).
  
  In version 4.0, we create T-SQL job steps in the following form:
  
  DECLARE @exitcode int
  DECLARE @sqlerrorcode int
  
  exec master..sqlbackup '-sql "<SQL Backup commdn>" ', @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
  
  Any errors are raised manually, and this bubbles up to the SQL Server Agent job.

  petey February 08, 2006 20:46

  - 0 +
• 
  Thanks. This is what I needed.

  abuttery February 10, 2006 09:03

  - 0 +

Add comment

Please sign in to leave a comment.