SQL Backup return code

Dje · May 9, 2008 12:54PM

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 &#40;@err &lt;&gt; 0 and @err &lt;&gt;300&#41;
	begin
		insert into failedBackup &#40;name&#41; values &#40;@name + ' &#40;Full&#41;'&#41;
		insert into SYS_backup_log &#40;dbName, action&#41; values &#40;@name, 'Full backup failed'&#41;
		set @failureOccur = 1
	end

where @err is the @return of the following backup line:

			SET @CommandString = '-SQL "BACKUP DATABASE &#91;' + @DBName + '&#93; TO DISK = ''' + @pathBackup + '\&lt;DATABASE&gt;\&lt;AUTO&gt;'' WITH DESCRIPTION = ''&lt;AUTO&gt;'', ERASEFILES_ATSTART = ' + @nbDayToKeepBackup + ', COMPRESSION = ' + @compression + ' , THREADCOUNT = ' + @threadcount + ' , LOGTO = &#91;' + @logFolder + '\&lt;database&gt;_&lt;datetime yyyymmdd_hhnnss&gt;.log&#93;"'
			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

petey · May 12, 2008 4:05AM

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.

Dje · May 13, 2008 3:03AM

Thanks petey.

SQL Backup return code

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk