Update: Don’t copy or transfer Maintenance Plans. Create new ones Instead if needed or checkout Ola Hallengren’s Maintenance solution or Minionware suite.
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!
MSSQLTREK 
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.
Tip before importing open all the packages in Notepad++ do a find and replace servername save all the packages and then import with Intergration Services
Thanks Jerome. Good tip. Especially as the maint plan has a hard-coded connection (which cannot be edited/removed via the SSMS GUI). While I can export, mod the file, ten import onto a different server ok, the jobs don’t come across. I’ll have to dig deeper across the net to find out why/how to bring them across too.
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.
This doesnt work in environment that has no Integration Services installed (only the Engine is installed). Is there a way to get this working still? We have over 100 Maintenance Plan to migrate and hate to recreate them plus it will consume resources.
Hi John – Am afraid to say this, but am almost sure…we can’t because Maintenance plans are nothing but SSIS packages.
I was under the same impression, however, I installed just the native client and left out the SSIS, SSRS, Replication, and SSAS unchecked while installing. Still have the installation document. After server install, went ahead and created the maintenance plan (maintenance plan is available even without having to install SSIS), and the server went live for more than 3 years now. We are ready to migrate and I faced this issue here. You can try installing a new SQL instance in your desktop to try it out. Dont install anything but the Engine and client. then create a dummy maintenance plan and see if you can export it. Guess Microsoft need to answer this!!!
If am not wrong, the old one should be a 2005 SQL Instance which you are running since past 3 years, right??…
I think this dependency started from SQL 2008R2(or 2008). I might be wrong….Just guessing here!
After server install, went ahead and created the maintenance plan (maintenance plan is available even without having to install SSIS), – I cant understand what do you mean Mr. John can you explain in detail. 🙂
Can any one help me out?
For eg : I have two instances in my machine. INST1, INST2. In INST1 i have created a Maintence plan to back up all the user databases. Now i want that Maintence plan to be copied or to be moved to INST2. Is it possible?
Why don’t you just create a new Miant Plan on Inst2 Instead? It’s simple to create a new basic Backups plan instead of copying a plan from one server to another and changing the server connection properties IMHO.
After I migrated maintenance plan from 2005 to sql 2012 I dont see the job was created for that maintenance plan. Why? How to achieve that easiest way?
Yes, it wont create jobs. All you are doing here is exporting SSIS package to a new server! That’s another reason, I recommend creating Maintenance plans from scratch instead of migrating. It’s waaaaay simple…that way 🙂
BTW, have you considered Ola Hallengren’s Maintenance Solution instead of relying on Maintenance Plans?
Jobs will show up once you schedule the maintenance plan. I have done it many times
Great post. Quickly solved my problem. I was even able to export directly to my other SS instances. Thanks.
In order to transfer packages from SQL Server without integration services installed you can still use option Import on new SQL Server Integration Services under msdb and as a source of package select SQL Server. I believe you can easily transfer SSIS packages to new sql server too, but first you need some tool to show export / import dialog, which is in SSIS.
hi, can you provide me a link to download a sql server which has maintanance plan included?
Thanks
SQL Server with Maintenance Plans included? That’s not how Microsoft ship this product. Once you download SQL Server, you can create Maintenance Plans as needed…
Excellent article
SQL 2005 have SSIS, but if you are using a newer Management tools, two things will happen, SQL 2005 does not like to work with newer management tools and make sure that you have access and permissions in the SQL 2005 in order to connect to SSIS. This article is excellent.
Thanks for the blog post! It worked like a charm for me!
A caveat, please check your connections after your export and import. Notice you are pointing to the old server. BAD BAD BAD. You need to create a new connection to the new server and point all your connections to the new connection.