Today I needed to rebuild a maintenance plan with specially selected databases. Anyone who has done it know that the interface for selecting databases lacks in one key area: the window cannot be resized and shows about 7 databases. My solution was to store the selected dbs for the plan in a temp table and then update the new plan with that info. Even the Green DBA knows a bit about the system tables, and isn’t afraid to play with them. That said, it’s a good idea to take a backup of the msdb database before doing anything to it.

SQL Server 2000 stores info related to dts and maintplans in the msdb. The two tables I’m concerned with are sysdbmaintplans and sysdbmaintplans_databases. There is an entry in the former for each maintenace plan, and an entry for each database used for the maintenance plan in the latter. They are associated by a guid. After knowing this, the rest is pretty easy. Definitely better than checking 80 boxes for me.

–insert database and plan id from plan we need to rebuild into a temp table
select my_db_name, plan_id into user_myDBsforOtherPlanfrom
(SELECT sysdbmaintplan_databases.database_name AS my_db_name, sysdbmaintplan_databases.plan_id as plan_id
FROM sysdbmaintplans
INNER JOIN sysdbmaintplan_databases ON sysdbmaintplans.plan_id = sysdbmaintplan_databases.plan_id
WHERE (sysdbmaintplans.plan_id = ‘{699A6A76-2BC3-4EA6-BAF1-0E268F82B095}’)) myDBsforOther

— go create your new maintplan and just select 1 db
–find the new guid and substitute in the queries below
select * from dbo.sysdbmaintplan_databases

–we need to find the plan id for the new plan and update our temp table, so they match. This could just go in the third step as a constant i suppose.

update user_myDBsforOtherPlanset plan_id = ‘{B9051EA2-F303-4E4F-BB17-FBF26E9B0C48}’

–insert the rows into the sysdbmainplan_databases table – except the one we added to the newly created plan

insert into dbo.sysdbmaintplan_databases (plan_id, database_name) select my.plan_id, my.my_db_name from dbo.user_myDBsforOtherPlan as my , dbo.sysdbmaintplan_databases notmine
where my.plan_id = notmine.plan_idand my.my_db_name <> notmine.database_name