Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to export a simple maintenance plan from an SQL Server instance.

I want to check the export into source control, and apply the exported maintenance plan to functionally identical instances.

StackOverflow and SQL Server Newbie recommend using Integration Services to export the maintenance plan.

When I try to connect to Integration Services on the Export target, I receive the following error:

Connecting to the Integration Services service on the computer "WEBSERVER" failed with the following error: The specified service does not exist as an installed service.

We chose to disable Integration Services on WEBSERVER because we use this box only for serving data to consumer applications. All data on WEBSERVER is replicated from a backend instance. Integration Services is used heavily for processing data on the backend instance.

Is there a documented way to export a maintenance plan without using Integration Services? Does Microsoft support it?

share|improve this question

Maintenance plans are stored in msdb.dbo.sysssispackages like any other SSIS packages that are stored to SQL Server. I have a handy post on SSIS Package Extract from MSDB that should cure what ails you.

share|improve this answer
    
This only works if you have SSIS fully installed, because dtutil - which this is built round - is basically disabled otherwise, even though it is present. Some versions of SQL Server (such as Web Edition) do not allow SSIS to be fully installed, even though Maintenance Plans essentially use almost all of the functionality of SSIS. (However, there is a hack to get round this, assuming you have two versions of SQL Server, one of which is not hobbled - see my answer below.) – bmju Mar 30 '15 at 10:45

There is a way to do this.

Assume that, like the OP, you have two SQL Server instances, one of which has SSIS installed and one of which does not (probably cannot, e.g. if it is SQL Server Web Edition).

Write a stored procedure which copies the user maintenance plan rows from the hobbled server to the non-hobbled one. The relevant rows are:

SELECT name 
FROM msdb.dbo.sysssispackages 
WHERE packagetype = 6

You would need to write this SP so that it deletes any rows with matching id's first, then inserts the latest versions (or similar approach, e.g. UPDATE matching ids, then INSERT missing ids). And you will need to set up a linked server on one or other side, so that you can write SQL which addresses both servers.

That's it, really, you can then call this regularly... from a maintenance plan, for instance... and back up all the maintenance plans on the non-hobbled side.

This is a massive hack, of course, but it actually works. (I would imagine that it's pretty important that the version number of SQL Server is the same on both sides, for the data in msdb.dbo.sysssispackages to be as compatible between different server instances as it actually seems to be.)

You could always just back up the relevant rows from the SSIS database table directly, of course. That would work anyway - as a full answer to the original question. As stated, this is nothing to do with presupposing SSIS - it just presupposes Maintenance Plans!

So that is a supported, light-weight method, which works without SSIS anywhere on the system. The advantage of the more complex, more hacky method above is that it gives exported plans in a standard format, not just as bare data rows; so I think that is much more likely to be importable into a different version of SQL Server, later on.

share|improve this answer

Have a look at:

How to Transfer (Copy) Maintenance Plans from One Server to Other? by Sreekanth Bandarla

To paraphrase and summarize:

  1. In SSMS, instead of connecting to "Database Engine...", connect to "Integration Services...", for the same server name.

  2. Navigate to 'Stored Packages/MSDB/Maintenance Plans'.

  3. There are all your maintenance Plans. Right-click on them to export them.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.