Thursday, March 3, 2011

Automate Deploying Maintenance Plans in SQL Server 2005, 2008, and 2008 R2

Have you ever set up a SQL Server before? If you’ve landed on this post, chances are yes you have! And for most of us we haven’t set up just one SQL Server Instance we’ve set up SQL Server many, many times.  When you have to do a repetitive task you figure out the best way to do the task as quick as you can.  For DBA’s this normally means automating the process, setting up a SQL Agent Job, a Scheduled Task, or using a 3rd party scheduling software because we’ve normally got other fires to put out.

So when you set up a new server what are some things that you automate?  Normally you have a standard set of Jobs, Alerts, and Maintenance Plans that you want to have in place.  You document them, you put together master Scripts that you will deploy so you don’t have to create them by hand.  But that doesn’t work for Maintenance Plans.

"So Ball's", you say "So I have to make them manually, big deal."  Ahh Dear Reader when your piecemealing them one at a time, it may seem like no big deal.  But let us say that we are upgrading HUNDREDS or even THOUSANDS of servers.  Your going to want to skip as many steps as possible.


If you Create a Maintenance Plan, let’s make a Simple one, and try to script it out you’ll notice you can’t.  So if you take a look at the tables that store the Maintenance Plans In MSDB, you'll find that the Maintenance Task is in varbinary(max) format.  You can try scripting it out, but that is the old fashion way.  

Sometimes we as DBA's get used to doing things the old fashion way, we need to get with the times.

Embracing the New Hotness SSIS!

There's a New Game in Town!  If I imported an SSIS package to your server, and you wanted to place it on another server, how would you go about this Dear Reader.  Why you'd use SSIS!  Scripting out jobs is so SQL 2000, we've grown and so has our technology, so let's treat a Maintenance Plan like we should, it's an SSIS package and we shall use SSIS to migrate it.

If you look closely at the picture you will see I have a SQL 2005 & a SQL 2008 R2 instance on my laptop.  For this demo, I will create a Maintenance Plan in 2005, and using SSIS Export the Maintenance Plan to my 2008 R2 instance.  

Step 1 Create the Maintenance Plan
Name the Maintenance Plan
This will be a simple plan with two subplan's, Integrity Check and Update Statistics.
Let's Save the Maintenance Plan and verify that it created SQL Agent jobs for each subplan.

Step 3 Let's log onto my local SSIS instance (*This could be a remote instance SSIS doesn't have to live on the server where you created your Maintenance Plan)

Because this is local I don't have to Import the Maintenance Plan it is already in my SSIS server stored under Maintenance Plans, and it will be titled what ever you name it.

Step 4 Export the Plan to the desired location

Type in the Server Name that you want to deploy your package to.  Click on the ellipsis button

Click on the Maintenance Plan folder, Click OK

And Click Okay
Here is a before, so you can see there was not Maintenance Plan.
And Here is the After the Maintenance Plan is in place. 

But Wait There's More

If you take a closer look Dear Reader you will find that your Schedule and your SQL Agent Jobs have not been created.  You must manually re-create these.  

No Schedule

And No Agent Jobs.  Creating the Schedule will take some clicking, but the Agent jobs will be created as soon as you open and save the Maintenance Plan.

Plan Owner

The Plan Owner will end up being the Windows Account that you authenticated to SSIS with, in order to set the Maintenance Plan Owner as SA you should run the following script.

SQL 2005

use msdb;
update dbo.sysdtspackages90
set ownersid = (
select sid from msdb.sys.syslogins where name = N'sa')

SQL 2008 & 2008 R2

UPDATE msdb.dbo.sysssispackages
SET [ownersid]=0x01

I hope you find this helpful, and it get's you along the way of standardizing your Maintenance Plans in 2005, 2008, and 2008 R2.  (In a couple month's I'll be redoing all this for Denali!)

Thanks For Reading,



  1. The local server connection points to the original server you created the maintenance plan, so you will either need to create a new connection (manage connections) or start over from scratch and recreate the plan manually.


  2. What happens with the maintenance plan if the target SQL2008R2 instance does not have SSIS installed?

  3. Regarding the local server connection.. If you export the maintenance plan as a file, you can manually edit the dtsx code and update the local connection string with the correct server name. I've done this with great success. This allows you to build a template that can be easily modified and imported from other servers.

  4. Brad, I have applied this logic saving the package with a different name on the same server. I wanted to use the first maintenance plan as a template create other maintenance plan on the same server. It actually saves it with a differnet name but the momnet you open and save it for the jobs to get created, the original maint plan gets deleted. Any Idea, how to overcome this?. Thanks in advance.


  5. this is easy enough Brad bit my issue is what do we do when the destination server is not in the same network? Our customer databases are miles and miles away east coast, west coast.. then what. In 2005 I had used the ugly traditional way but thought 2008 r2 has a simple way? any idea?

  6. I used LOCALHOST to connect to the database engine on one of my servers, created the maintenance plan, saved it and exported it to dtsx file. On my second server, I imported the dtsx and works like a champ. I did not have to modify the connection string, etc.

  7. Helpful tricks and tips. Being a beginner, found it very useful.