1

I have a Backup DB task setup to do a full backup of All User Databases. However, I find that when new databases are added, they do not get automatically included in the backup plan.

I am forced to open the plan each time a new database is added and select All User Databases Radio button and save the package for the new DB to be added to the backup task.

Also the New DB does not show up in the SelectedDatabases collection in the properties for the backup task.

Any ideas?

Thanks, Raj

3 Answers 3

1

Yes, this is unfortunately by design. The maintenance plan will store a persistent snapshot list of the selected databases at the point the plan is saved and does not dynamically query for all user databases each time it is run. Very frustrating.

2

Yes-- write a stored procedure to query the system catalogs and add / remove databases to the backup plan and run that procedure on a schedule. Any competent SQL Server DBA who knows TSQL should be able to do this. (I've written scripts to do backups of "MSDE" instances in the past just this way.)

1
  • Yes, very much true. In lieu of building maintenance plans writing your own backup jobs provides much more flexibility.
    – squillman
    Jun 18, 2009 at 15:09
0

There's a very cool script (written by a guy called Ola Hallengren) that I like to recommend to people when teaching database maintenance - it allows you to automate backups, index maintenance, consistency checking.

Check it out at SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

You must log in to answer this question.