New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!