Transfer SQL Server Maintenance Plans Between Servers

By: Sergey Gigoyan | Comments (1) | Related: > Maintenance

Problem

In the previous articles, we have learned how to create, configure and use SQL Server Maintenance Plans. Sometimes, it is needed to move these plans from one environment to another. This article will explore the process of transferring maintenance plans from one server to another.

Solution

The creation of a SQL Server Maintenance Plan assumes the creation of a SQL Server Integration Service package. In other words, a Maintenance Plan is a SQL Server Integration Service package and the corresponding SQL Server Agent Job runs this package. If we open the corresponding job’s properties for a maintenance plan (let’s do it for the RebuildIndexes, for example), we can see that the type of the job is SSIS package:

Therefore, these packages can be managed using SQL Server Integration Services. To connect to the SSIS via SQL Server Management Studio (SSMS), we should click on “Connect” and choose “Integration Services”:

After connecting to SSIS, we can locate our packages in the Object Explorer, under Stored Packages > MSDB > Maintenance Plans:

Suppose, we have a task to move some of these maintenance plans, Rebuild Indexes for instance, to another environment. If we perform a right-click on the corresponding package, we can see that there is an option for exporting the package:

If we click on the “Export Package…”, we can see that we have three options where we can export the package – File System, SQL Server, and SSIS Package Store:

We have chosen to export to the file system and mentioned the corresponding path. After clicking “OK”, the package appears in the mentioned folder:

The next step is the importing package to another server. In our example, it will be SQL Server 2014 environment. Before importing the package, we need to manually edit it. To do so, we perform a right-click on it and open it via a text editor (via Notepad for example) and change the “Data Source” in the connection string by the destination server name:

Then, in the Integration Services of the destination server, we locate Stored Packages > MSDB > Maintenance Plans and right-click on it, then choose “Import Package…”:

We choose the corresponding package’s path and click “OK”:

After that, we can see that although the corresponding package appears under the Maintenance Plans, there is no job under the SQL Server Agent Jobs:

We can add them by editing the package and setting the connection parameters:

As a result, the job appears under the SQL Server Agent Jobs.

Additionally, we need to set the schedule manually. Now, we can successfully execute the plan:

Now, let’s check what happens if we configure a task with options available on SQL Server 2017 and move the task to a SQL Server 2014 environment, where these options are not available from the UI. In the picture below, we can see that there have been some additional configured options in UI the (highlighted in red):

After that, we moved the package into a SQL Server 2014 environment, opened it and checked the T-SQL code:

It is clear, that all of these additional options are not kept and have their default values assigned. This is also an important point to consider while exporting a package from a newer environment to an older one.

Conclusion

In conclusion, maintenance plans can be moved between servers by using SQL Server Integration Services. The SSIS allows importing as well as exporting packages to the server. As each maintenance plan itself is a SSIS package, it can be easily transferred via SSIS.

Next Steps

To find more information about the discussed topic, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

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

View all my tips