Copy maintenance plans to a different server

Question

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).

Answer 1

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.

Answer 2

I found a more hacking way.

Every Execution Plan is one record in the msdb.sysssispackages table.

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.

Answer 3

1. log as "sa" otherwise will not be displayed.

2. to see quickly: