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
