Server jobs not being updated on SQL Server

bboice

I'm running SQL Backup on SQL Server 2000. When my DBA deletes a database that had been included in a Full backup, SQL Backup still attempts to backup the database resulting in an error (see below).

When I edit the backup job in SQL Backup, the database is not displayed in the list of databases. Even though I save the edit, the actual job on the SQL Server is not updated. The deleted database is still being referenced in the job.

Is there something else I need to do to get SQL Backup to recognize the database is no longer available and to update the job file?

Thanks.


9/29/2008 9:08:53 PM: Error 880: BACKUP DATABASE permission denied in database: (sharepointsite)
9/29/2008 9:08:53 PM: SQL error 911: Could not locate entry in sysdatabases for database 'sharepointsite'. No entry found with that name. Make sure that the name is entered correctly.

petey

You'll need to check if you can use a different database name list to perform the backup, and if possible, you'll need to manually edit the backup job created by the GUI. The job uses the names of all the databases existing during the time you created the job e.g.

BACKUP DATABASES [db1, db2, db3] TO ...

You could set up the job to back up all databases using the wildcard option e.g.

BACKUP DATABASES [*] TO ...

or you could use an exclusion list e.g.

BACKUP DATABASES EXCLUDE [master, model, msdb] TO ...

In both situations, SQL Backup will only backup databases that exist during the time of the backup.

bboice

Hello Peter,

Thank you for the information. I do not fully understand your first statement about a different database name list. All databases that do still exist on the server are successfully backed up. Only the deleted databases cause the overall job to fail.

I can edit the job manually using Enterprise Manager; however, I thought that SQL Backup would take care of it especially when I re-edited the job and the deleted databases were no longer part of the list.

petey

After you modified the job and saved the new database list, could you please check the T-SQL command that's stored in the job step? Does it still include the deleted database? By design, it should not.

What I meant by modifying the database name list is that if you are constantly adding or removing databases, explicitly naming databases in the SQL Backup backup command may not be convenient as you'll need to constantly update the job. Using a wildcard or an exclusion list may be a better option.

bboice

Thank you for the clarification about the wildcard versus using specific database names.

When I did edit the job in SQL Backup and saved it the two deleted databases did remain in the job file on the server.

We're in the process of migrating off of this SQL 2000 server. It has been problematic. I'll keep an eye on this issue on SQL 2005.

petey

Which version of the SQL Backup GUI are you using (see Help > About)? I'm using 5.3.0.178, and updating the job does rewrite the database name list to only the selected databases.

bboice

I'm using the same version of SQL Backup.