question

I have exported maintenance plan 'History_Cleanup' to file using SSIS. Then I copied that plan to another server using DTutil. Once copied, I have not yet created a schedule/job for the (only) subplan 'Cleanup'. To achieve that, I was planning to use msdb.dbo.sp_maintplan_update_subplan, but I am missing the @plan_id and @subplan_id from the newly created plan. So I queried the maintenance plan repository tables in MSDB. But the strange thing is: my new plan doesn't have a subplan: select p.name as planname, sp.subplan_id as subplan_id, sp.subplan_name as subplan_name from msdb..sysmaintplan_plans p left join msdb..sysmaintplan_subplans sp on p.id = sp.plan_id where p.name = 'History_Cleanup' go planname subplan_id subplan_name ---------------- ------------- ------------ History_Cleanup NULL NULL However, if I open the maintenance plan in SSMS, the subplan 'Cleanup' **does** exist! After checking a lot of possible DTS/SSIS/Maintenance Plan repository tables (like msdb.dbo.sysdtspackages90) I don't see the subplan name appear anywhere! I tried if not having a schedule might be a problem, but removing the schedule from an existing subplan, that plan still returns correct results for the subplan columns in the above query. Does anybody have an idea where SQL Server stores the name for the subplan after copying the plan using DTutil? Or even, how to retrieve the subplan_id in my situation? Or is it possible to export package, job and schedule in one go? Many thanks in advance.