Why I don’t like maintenance plans

If you create a maintenance plan (SQL 9.0.3042) to backup databases and select All databases as below;

Then at some point in the future if one of the databases is taken offline the job created by the maintenance plan will fail.

From what I have seen the job will still backup the rest of the databases (which is the only saving grace), you just get a lot of errors in you SQL log like the below;

BACKUP failed to complete the command BACKUP DATABSE SQLServer365. Check the backup application log for detailed messages.

Shortly followed by;

BackupDiskFile::OpenMedia: Backup device 'D:\Backups\SQLServer365\ SQLServer365_backup_201203160500.bak' failed to open. Operating system error 2(error not found).

Yes I know, you should know that the database was taken offline and have amended the maintenance plan accordingly.  In my experience though I have found that people create maintenance plans as a quick temporary fix on non-critical servers  (which becomes permanent).  These servers are often forgotten about until there is a problem.

I avoid creating maintenance plans, yes they are quick and easy but are often everything or nothing.  You are much better creating maintenance routines tailored to your environment.  I am in the process of creating a maintenance routine for backups, consistency checks and index maintenance to work across all flavours of SQL Server with a whole host of configurable parameters. 

I will provide this once complete.

Chris