Transfer SQL Server Maintenance Plans Between Servers


By:   |   Updated: 2020-04-20   |   Comments (1)   |   Related: More > 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:

sql server integration services 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”:

integration services

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

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:

rebuild indexes

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:

file system

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

packages to export

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:

connection string

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…”:

import package

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

import package

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:

rebuild indexes

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

connection properties

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

maintenance plans

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

rebuild indexes

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

sql server 2017 to sql server 2014

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

transact sql

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:



Last Updated: 2020-04-20


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources





Comments For This Article




Sunday, April 26, 2020 - 3:17:03 PM - Sameer Back To Top (85477)

Hi Sergey,

Thanks for the above article, I have a requirement where I need to migrate around 300 Maint plans from sql 2017 to SQL 2019 server, any T-SQL script that can be used to do it instead of manually?



download





Recommended Reading

Reduce Time for SQL Server Index Rebuilds and Update Statistics

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

Move SQL Server Maintenance Plan from One Server to Another

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

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks








get free sql tips
agree to terms


Learn more about SQL Server tools