Error 880: BACKUP DATABASE permission denied in database v6

chinesinho

Hi there,

Running into the following error when backing up a database on SQL2005 SP4 (9.0.5000) for sql backup v6.4.0.56:

Error 880: BACKUP DATABASE permission denied in database: (database_name)
SQL error 911: Could not locate entry in sysdatabases for database 'database_name'. No entry found with that name. Make sure that the name is entered correctly.

The failure only occurs weekly though on the same day each week. It works fine on other days and the database is present when this error occurs.

Does anyone know what the cause of this might be?

Thanks!

petey

The error message and consistent timing suggests that the database was not listed in the master..sysdatabases table at the time of the backup. Try adding a pre-backup validation step to the job e.g.

in the SQL Backup job step, the command would look something like this:

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [database_name] TO DISK = ''<AUTO>'' WITH DISKRETRYINTERVAL = 0, DISKRETRYCOUNT = 0"', @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

Add the lines in bold red to the existing job step:

IF EXISTS (SELECT 1 FROM master..sysdatabases WHERE name = 'database_name')
BEGIN

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE master TO DISK = ''<AUTO>'' WITH DISKRETRYINTERVAL = 0, DISKRETRYCOUNT = 0"', @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

END
ELSE BEGIN
RAISERROR ('Pre-backup validation failed: no entry in master..sysdatabases table for database.', 16, 1)
END

In this way, we can tell if it's a problem with the SQL Backup validation, or if the database is indeed missing from the sysdatabases table.