Backup job issues after upgrading to 6.2

Widget

I've recently upgraded from SQL Backup 3.2 to 6.2 running backups against a SQL 2000 instance, and we're having a few issues. It looks like the backups are performing correctly, but the job is reporting as failing.

I just want to confirm that the syntax for the backup portion of the job is still correct, i've had a look at the backup command information but i cant find anything about the encryptedpassword syntax that we're currently using so if someone can cast an eye on it and confirm that would be much appreciated, the relevant part of the SP that we're using is below.

SET @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''' + @filename +
''' WITH NAME = ''Database (' + @databasename + ') Full'', INIT
, PASSWORD = ''<ENCRYPTEDPASSWORD>passwordinfohere</ENCRYPTEDPASSWORD>''
, VERIFY, ERASEFILES_ATSTART = 1h, COMPRESSION = 3" -E'

thanks in advance!

Brian Donahue

Hi Lindsey,

Usually the opposite is true -- jobs fail without reporting back to the SQL Agent and end up as a success. This is because SQL Backup's extended stored procedure does not raise errors back to the SQL Agent; it sets a return code and a SQL-specific return code.

The bit that is flagging the job as failed appears lower down in the script than the snippet you'd sent. If you look for the RAISERROR command, this is the bit you're interested in.

Normally, you want to flag only error codes greater than 500, otherwise your job will be flagged as an error when mail could not be sent or the SQL Backup Agent had to retry because there was not enough free contiguous VAS memory. For example:

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup '-SQL "BACKUP LOG [database] TO DISK = ''D:\sql2005\MSSQL.1\MSSQL\Backup\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COPYTO = ''\\server\Backup'', THREADCOUNT = 2"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END