Move SQL Server Maintenance Plan from One Server to Another


By:   |   Updated: 2018-03-22   |   Comments (3)   |   Related: More > 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:

  1. Export Maintenance Plan from the source server.
  2. Edit the connection string in the Maintenance Plan.
  3. 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.

connect to integration services

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.

integration services export package

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.

integration services export package

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:

  1. Right click on the exported maintenance plan and open it in Notepad.
  2. Find the connection string attribute DTS:ConnectionManager in the XML file as shown below.
  3. 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.

integration services import package

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.

integration services import package

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.

integration services packages

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.

ssms maintenance plans

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.

sql agent jobs

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

run sql agent job

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

run sql agent job

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


Last Updated: 2018-03-22


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article




Friday, August 03, 2018 - 4:03:40 PM - Subramanya Bharadwaj Back To Top

 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 Back To Top

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 Back To Top

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? 

 



download


Recommended Reading

Reduce Time for SQL Server Index Rebuilds and Update Statistics

Update Statistics for All Tables and Databases in a SQL Server Instance

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table

Getting Started with SQL Server Maintenance Plans - Part 1





get free sql tips
agree to terms


Learn more about SQL Server tools