What are the challenges you face when working across database platforms? Take the survey
Options

SQL Backup Completes with Success but Backup Failed

edited February 10, 2006 9:03AM in SQL Backup Previous Versions
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.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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
  • Options
    Thanks. This is what I needed.
Sign In or Register to comment.