I am starting a new category in my blog - Roman's Weekly SQL Server Tip. The key word here is “weekly”, it will force me to provide something useful on regular basis and hopefully increase the amount of beneficial, and actually SQL Server related content on sqljunkies.com. Here is the first one:
Someone asked the following question in response to my Inside Database Maintenance Plans article - How can I automatically include any new databases in a maintenance plan but still be able to exclude specific databases? I came across this scenario many times, especially on development servers or in environments without a dedicated dba where you want to make sure any new database added to the system gets automatically backed up, yet for whatever reason you want to exclude certain databases. The problem is that the maintenance plans UI allows you to define options for "All databases" and "All user databases", but there is no way to exclude any databases.
So the answer is No, this can't be done with Enterprise Manager, but you can achieve that functionality with a custom script that modifies the sysdbmaintplan_databases system table in msdb. The following stored procedure shows you how to do that. Just modify the text to match the name of the existing plan, create the procedure in msdb or master and create a scheduled job to run it as often as desired. Any new databases will be added to the plan when the stored procedure executes, after that any maintenance actions associated with the plan will run on the new database. The stored procedure excludes all system databases, pubs and northwind, you can modify it to exclude other databases.
CREATE Procedure AddDatabasesToMaintenancePlan
AS
-- Author: Roman Rehak
SET NOCOUNT ON
DECLARE @PlanName varchar(300)
DECLARE @PlanID nchar(36)
SELECT @PlanID = plan_id FROM msdb.dbo.sysdbmaintplans
WHERE plan_name = 'My Plan' -- <-- modify here
IF @PlanID IS NULL
RETURN
INSERT msdb.dbo.sysdbmaintplan_databases
SELECT @PlanID, name FROM master.dbo.sysdatabases
WHERE name NOT IN
(SELECT database_name FROM msdb.dbo.sysdbmaintplan_databases
WHERE plan_id = @PlanID)
AND name NOT IN
('master', 'msdb', 'tempdb', 'model', 'distribution',
'pubs', 'northwind') -- <-- modify here
GO