SQL Backup Completes with Success but Backup Failed

abuttery@impactrx.com

I think I found a bug in SQL Backup (standard) 4.0:

I had a RAID controller in a production server fail last night during the backup processing. The job continued to report that each database was backed up successfully but the jobs were failing because the backup files could not be written due to the failed controller. Here is a sample backup email from SQL Backup:

SQL Backup log file
2/7/2006 6:49:19 PM: Backing up RT (full database) to:
L:\SQL_Backups\RT.sqb

2/7/2006 6:49:19 PM: Failed to create backup folder : L:\SQL_BackupsI expect the backup to fail when this occurs.

petey

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

abuttery@impactrx.com

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

petey

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.

abuttery@impactrx.com

Thanks. This is what I needed.