Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).
Go to the server where the jobs exist, in Management Studio's Object Explorer go to Management > SQL Server Agent and highlight the Jobs folder. Hit F7 (or View > Object Explorer Details). You should see multiple jobs in the right pane. Highlight the ones you want to copy, right-click, Script Job As > Create To > Clipboard. Now connect to the other server, open a new query window, paste, and hit F5. Much easier than mucking with SSIS.
You may have to take additional steps, depending on what the plans do. Connect to the Integration Services instance, rather than the database instance, expand Stored Packages > MSDB > Maintenance Plans, and right-click any plan and choose Export package. On the other server, follow the same steps, but right-click Maintenance Plans and choose Import package. You will have to do this for each plan.
I found a more hacking way.
Every Execution Plan is one record in the
You can copy records from the
msdb.sysssispackages table of source server to the
msdb.sysssispackages table of the destination server.
Simply create a linked server on source server and use this Insert from the source server:
INSERT INTO YOUR-DEST-SERVER-NAME.msdb.dbo.sysssispackages SELECT name, id, description, createdate, folderid, ownersid, cast( cast( replace( cast( CAST(packagedata AS VARBINARY(MAX)) AS varchar(max) ), 'YOUR-SOURCE-SERVER-NAME', 'YOUR-DEST-SERVER-NAME') as XML) as VARBINARY(MAX)) as packagedata, packageformat, packagetype, vermajor, verminor, verbuild, vercomments, verid, isencrypted, readrolesid, writerolesid FROM msdb.dbo.sysssispackages AS sysssispackages_1 WHERE (name = 'YourMaintenancePlanName')
INFO: Replace the server name is crucial to handle destination connection on the Maintenance Plan designer.
IMPORTANT: Coping records only copy the structure of maintenance plan. To recreate jobs, when you finish the copy, you MUST edit every Mantainence Plan, reset the schedule and save it.
1Suggest using trailing backslashes in the replace('ab\', 'yz\') to avoid replacing other parts of the data.– crokusekNov 26, 2019 at 0:22
OP was asking about maintenance plans which are separate from jobs and cannot generate create scripts in the way you can for jobs.– JordanMay 16, 2018 at 20:42