Move SQL Server Maintenance Plan from One Server to Another

By: Nisarg Upadhyay | Comments (4) | Related: > Maintenance

Problem

How do I transfer an existing SQL Server database maintenance plan from one server to another server? Read this tip to see the steps you need to follow.

Solution

Sometimes when there are many servers it's helpful to setup a task on one server and replicate that task to all of the other servers. One of the tasks you may be using are SQL Server Maintenance Plans and you know how many steps you need to go through to setup the plan, so having the ability to replicate this same plan on other servers is very helpful.

In this tip, I am going to demonstrate the process of exporting an existing SQL Server Maintenance Plan and import it to another server without having to recreate the entire maintenance plan. This will reduce time, effort and the chance of making mistakes for the DBA and make the process much easier.

When we create any database maintenance plan, SQL Server will automatically create a SQL Server Integration Services Package (SSIS) with same name and deploy it in the MSDB database. If the maintenance plan is scheduled to execute at a specific time, a SQL Server Agent Job will be created automatically. Using Integration Services, we can export a SSIS package associated with a maintenance plan and import it to another server.

Sample Maintenance Plan

For this demonstration, I have created a database backup maintenance plan. It generates backups of master, MSDB and model databases and stores the backups to disk. The job is scheduled to execute at 12:00 AM each week.

Steps to Move Maintenance Plan

After the plan has been setup, we will perform the below tasks:

Export Maintenance Plan from the source server.
Edit the connection string in the Maintenance Plan.
Import the Maintenance Plan on the destination server and execute it.

Export Maintenance Plan from Source Server

Connect to the Integration Services catalog on the source server using SQL Server Management Studio (SSMS) by opening SSMS and clicking on the Server type drop down box and selecting Integration Services as shown below.

We have a few Maintenance Plans and we are going to export the BackupFull maintenance plan. To do this, navigate to Stored Packages > MSDB > Maintenance Plans in the object explorer. Right click the BackupFull maintenance plan and select Export Package as shown below.

After clicking Export Package a dialog box ‘Export Package’ will open. Select File System in the Package Location to specify we want to save to the file system. In the Package path text box, enter the desired location and name of the file you want to save and click OK.

Change Connection String in Maintenance Plan

Now, we must update the connection string within the maintenance plan, so that it can access the databases on the server where we will import the package. If we don't do this, when we execute the maintenance plan on the new server, it will try to connect to the source server and give an error. To avoid the error, edit the package as noted below.

SSIS packages are in XML format, which can be edited using Notepad or any other XML/text editor.

To change the connection string, perform the below steps:

Right click on the exported maintenance plan and open it in Notepad.
Find the connection string attribute DTS:ConnectionManager in the XML file as shown below.
Change the server name in the connection string as shown in the below code. Replace <Destination Server Name> with name of the server where the package will be imported and save the file.

<DTS:ConnectionManager
  DTS:refId="Package.ConnectionManagers[Local server connection]"
  DTS:CreationName="ADO.NET:SQL"
  DTS: DTSID="{FD7FD376-C8B3-44BE-ABB0-BDC47C51AFE7}"
  DTS:ObjectName="Local server connection">
  <DTS:ObjectData>
    <DTS:ConnectionManager
      DTS: ConnectionString="server=<Destination Server Name>;Trusted_Connection=true;Application Name='Microsoft SQL Server Management Studio';Pooling=false;Packet Size=4096;multipleactiveresultsets=false;" />
  </DTS:ObjectData>
</DTS:ConnectionManager>

Import Maintenance Plan to Destination Server

To import the maintenance plan on the destination server, connect to the Integration Services catalog on the destination server and navigate to Maintenance Plans as explained before. Now, right click on Maintenance Plan and select Import Package as shown in the below image.

After clicking import package, a dialog box ‘Import Package’ will open. Select File System in Package location and enter the Package path for the file you just edited.

Once the package is imported, you will be able to see the imported package underneath the Maintenance Plans on the destination server as shown in the below image.

Verify and Test Package

Now let’s verify that maintenance plan has been created properly.

As I mentioned earlier, the maintenance plan will take backups of the system databases master, model and msdb. To view the maintenance plan, open SSMS and connect to database engine. In the object explorer navigate to Management > Maintenance Plans and you should see the imported maintenance plan.

When we import the Maintenance plan, a SQL Server Agent Job associated with the maintenance plan will be created automatically. In this demo, backup job ‘BackupFull.Subplan_1’ was created as shown in the below image.

Right click the job ‘BackupFull.Subplan_1’ and click execute and the job will start.

After few minutes, the job should complete successfully and the backups will be created.

Summary

In this tip, I demonstrated the process of exporting a maintenance plan from one server and importing it to another server.

I would like to take this opportunity to thank my friend Dolly Gupta, who helped me proof read my tip.

Next Steps

Overview of database Maintenance plans.
How to create Maintenance plan to generate database backup
If you have issues connecting to Integration Services check out this tip

About the author

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, September 10, 2020 - 1:47:55 PM - Xiaogang Zheng

Thank you very much.

Your demo looks like using SQL Server 2014. I am using SQL 2014 as well but I cannot see the related agent job being created automatically after import the package success. The version I am using is Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) .

Then I have to create the job manually after import the MSDB package.
I use SSMS connect to DB engine to generate agent job script, change the server name to destination server name, then run it on destination server to create the agent job, but I still get error when running the agent job. The error message is :

Executed as user: INTTTCCA\sqlservices. Microsoft (R) SQL Server Execute Package Utility Version 12.0.6024.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:42:46 PM Error: 2020-09-10 13:42:46.28 Code: 0xC002F210 Source: {4CC03679-9D91-42DE-91F2-F13398EF0413} Execute SQL Task Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp..." failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2020-09-10 13:42:55.19 Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2020-09-10 13:42:55.19 Code: 0xC0024104 Source: {73625874-EABA-4A0F-8CD0-E9EC1F166F96} Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:42:46 PM Finished: 1:42:55 PM Elapsed: 9.188 seconds. The package execution failed. The step failed.

Any idea?
Thanks,
Xiaogang

Friday, August 3, 2018 - 4:03:40 PM - Subramanya Bharadwaj

Excellent, helped me.

You might also want to edit the exported file and mention to what DBs this is applicable.

Saturday, March 31, 2018 - 9:40:40 AM - Nisarg

Hello Bobby Russell,

Thanks for the valuable feedback.

You can script out SQL Job associated with the maintanence plan from source server and create it on destination server. Please refer below links:

https://dba.stackexchange.com/questions/89349/save-multiple-sql-jobs-in-single-files

Friday, March 30, 2018 - 11:22:19 AM - Bobby Russell

First, thank you so much for publishing this. The connection change was what I was hung up on. I now have one other question. When I imported my package, none of the schedules imported with it. I am able to clone the agent task from the other server but without the schedule stored in the maintenance plan, the first time I make a plan change and save it, I'm going to loose my schedule, correct? Is there a way to bring the schedule over in the maintenance package?