Competition: What’s your favorite Redgate tool? Enter now.

SQL Backup return code

DjeDje Posts: 13
edited May 13, 2008 3:03AM in SQL Backup Previous Versions
Hi,

I've got a question about how SQL Backup handles the retries.
I've got this piece of code :

exec SYS_BackupDatabaseFull @name, @err output, @backupType
	-- Check if the backup succeeded
	if (@err <> 0 and @err <>300)
	begin
		insert into failedBackup (name) values (@name + ' (Full)')
		insert into SYS_backup_log (dbName, action) values (@name, 'Full backup failed')
		set @failureOccur = 1
	end

where @err is the @return of the following backup line:
			SET @CommandString = '-SQL "BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @pathBackup + '\<DATABASE>\<AUTO>'' WITH DESCRIPTION = ''<AUTO>'', ERASEFILES_ATSTART = ' + @nbDayToKeepBackup + ', COMPRESSION = ' + @compression + ' , THREADCOUNT = ' + @threadcount + ' , LOGTO = [' + @logFolder + '\<database>_<datetime yyyymmdd_hhnnss>.log]"'
			EXEC master..sqlbackup @CommandString, @return output, @sqlerrorcode output	

I'd like to know, in the case of a retry, how SQL Backup works.
I can notice in the log generated a return code of 300.
Is SQL Backup directly returning 300 and then the next return code ?
Will my stored procedure get the 300 before the retry succeed - and so continue without a successfull backup done ?

How to adapt the sp to wait until it succeeded or really fails ?
How to get the second/third... return code ?



Regards,
Jerome

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup always returns the first error code (>= 500), or the first warning code (< 500) if there are no errors.

    In the case of retries, if the backup succeeded on subsequent attempt(s), there are no errors, so SQL Backup returns warning code 300. If none of the reattempts succeeded, an error code will be returned, as that is more 'serious' than a warning code.

    There is only one return code returned for each SQL Backup task.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks petey.
Sign In or Register to comment.