Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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.)

share|improve this answer
    
Yes, very much true. In lieu of building maintenance plans writing your own backup jobs provides much more flexibility. –  squillman Jun 18 '09 at 15:09

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.