Error 880: BACKUP DATABASE permission denied in database v6

Comments

• Options

  petey Redgate › Posts: 2,358 New member

  January 12, 2015 11:18PM

  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.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter