How to Transfer(Copy) Maintenance Plans from One Server to other???….
This would be a very short blog post where I’m going to show how maintenance plans can be copied over from one SQL Server to another. If you are not aware, Maintenance Plans are nothing but SSIS packages which SQL Server creates behind the scenes and deploy them under MSDB Database as you can see below.
I’ve created a Maintenance Plan(Test_Maint) in my Prod Instance and connected to local SSIS, as you can see in the above screenshot SQL automatically created an SSIS package with the same name and deployed under MSDB. So…to Copy this Maintenance Plan to another Server, all you need to do is…Just Right click on the package and Export to FileSystem. Now, copy the DTSX file(Actual SSIS package) to the new server and you can deploy under MSDB->Maintenance Plans Node by importing it.
Follow the below Screenshots in the same order.
Saved in my File System as shown below.
Now,connect to new Server and Import the package as shown below
Click Ok…and Connect to Database Engine and Expand your Management->Maintenance Plan Node to Verify Package being Created 
That’s it Guys!!!
Hope this is Useful!
Leave a Reply
CopyRights
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.
The process for copying a maintenance plan is fairly simple, but it involves quite a few steps. The biggest issue is that maintenance plans have a hard-coded connection and if you just export/import – the plan on the new server would actually be setup to run on the source system.
Agree! Personally I would say, It’s much easier to create Maint Plans from scratch on the New Boxes instead of copying/moving things around…..
Is it possible to configure the SelectedDatabases in maintenance plan SSIS package using configuration settings? I was trying to create one generic package so that it can be dynamically configured to be used across Org/servers where they can configured there own databases and schedules. I could not find it in any of its task’s property listing.
If you want that to be Dynamic, you can create an SSIS package using BIDS and select Maintenance Tasks. You can be as creative and dynamic as you wish with variables and what not……:)
Yes I was building SSIS package using BIDS; but could not locate SelectedDatabases property for configuration; I found from other forum that SelectedDatabases is a collection variable and it is not possible to set using config file. The task I was trying was Backup & Integrity tasks.