17

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

20

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.

9

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.

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

  2. to see quickly:

enter image description here

  • OP was asking about maintenance plans which are separate from jobs and cannot generate create scripts in the way you can for jobs. – Jordan May 16 '18 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.