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:
![sql server integration services package](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.001.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.002.png)
After connecting to SSIS, we can locate our packages in the Object Explorer, under Stored Packages > MSDB > Maintenance Plans:
![maintenance plans](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.003.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.004.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.005.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.006.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.007.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.008.png)
We choose the corresponding package’s path and click “OK”:
![import package](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.009.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.010.png)
We can add them by editing the package and setting the connection parameters:
![connection properties](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.011.png)
As a result, the job appears under the SQL Server Agent Jobs.
![maintenance plans](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.012.png)
Additionally, we need to set the schedule manually. Now, we can successfully execute the plan:
![rebuild indexes](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.013.png)
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](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.014.png)
After that, we moved the package into a SQL Server 2014 environment, opened it and checked the T-SQL code:
![transact sql](/tipimages2/6369_export-and-import-sql-server-database-maintenance-plan.015.png)
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:
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/maintenance-plans?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/integration-services/service/package-management-ssis-service?view=sql-server-ver15
About the author
![MSSQLTips author Sergey Gigoyan](/images/Sergey-Gigoyan-2.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips