SQL Backup Completes with Success but Backup Failed

Comments

• petey Redgate › Posts: 2,358 New member

  February 8, 2006 6:39PM

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

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• abuttery@impactrx.com Posts: 20

  February 8, 2006 8:07PM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  February 8, 2006 8:46PM

  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.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• abuttery@impactrx.com Posts: 20

  February 10, 2006 9:03AM

  Thanks. This is what I needed.

  0 · Share on Twitter