Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

4 Answers 4

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
    
Thanks. I'll try this when I get back to the office. –  Iain Elder May 11 '12 at 18:54
    
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 answer below.) –  bmju 2 days ago

Check this link Maintenance Plans from One Server to other

share|improve this answer
6  
Please expand on this answer. Link-only answers aren't helpful and are prone to linkrot, which will make them useless. –  JNK Jan 28 '13 at 13:41
1  
How is the referenced article related to the question asked? The method described there requires SSIS to be installed and running –  Fulproof Nov 14 '13 at 21:42
    
This is very relevant, @Fulproof, if only because Maintenance Plans are actually built around (almost all of) SSIS. However, parts of SSIS cannot be installed in some hobbled versions of SQL Server (such as Web Edition). But even there, there is still a way to build on this approach and fully solve the problem, if you have a least one non-hobbled SQL Server somewhere in your setup; see answer below. –  bmju 2 days ago

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.)

share|improve this answer
    
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. –  bmju yesterday

@Fulproof:

Based on what @RicardoBalda says (which all works as advertised), it seems clear that the vast majority of SSIS is installed and used by SQL Server Maintenance Plans, even if SSIS is not officially 'installed' as such, on a given server. (Which might sway someone's decision as to the perceived overhead of installing the rest of SSIS, to get at this easy way of importing and exporting Maintenance Plans.) (However, if you can't fully install SSIS - because your version of SQL Server does not allow it - then see the alternative answer below.)

To address @JNK's point, @RicardoBalda's article (abbreviated and paraphrased) says:

  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
    
You can create and run maintenance plans without installing the SSIS service. The question is about how to export the plan when the SSIS service is not available. –  Iain Elder Mar 24 at 20:46
1  
@Iain Elder - Apologies, you are right and Integration Services does not need to be installed to have Maintenance Plans. However... "SELECT name FROM msdb.dbo.sysssispackages WHERE packagetype = 6" shows the list of user Mainteance Plans, even if SSIS is not there (I have just tried this). That, plus other info on this topic, seems to show that a significant chunk of SSIS is installed anyway, and used by Maintenance Plans, whether or not SSIS is officially installed. (To me, this sways my own decision as to the perceived overhead of installing SSIS, since it seems to mostly be there anyway.) –  bmju Mar 27 at 10:23
    
Here's some lightweight SQL I'm using to export all Maintenance Plans: SET NOCOUNT ON; SELECT 'dtutil /sql "Maintenance Plans\' + name +'" /encrypt "file;OutputDir\' + name + '.dtsx;0" /quiet' FROM msdb.dbo.sysssispackages WHERE packagetype = 6 This can be saved in ExportScript.sql, then called from a batch file with: sqlcmd -E -h -1 -i ExportScript.sql -W -o "_autogenerated.cmd" del ..\OutputDir\*.dtsx call _autogenerated.cmd That's a full, lightweight, automatable solution (which I hope remains relevant, for the reasons I said, even though it requires SSIS to be installed). –  bmju Mar 27 at 10:55
    
Dear Iain, I have caught up with your problem. We didn't disable SSIS, but chose SQL Server Web edition. Using dtutil on that machine gives: This application requires ... Integration Services ... to be installed by SQL Server ... Standard, Enterprise, Developer, Business Intelligence, or Evaluation Edition.. I tried installing SSDTBI: no joy, same result. I tried connecting a Standard Edition dtutil to it. It connects fine, but says: The attempted operation is not supported with this database version. So now I have your question too, but I suspect the answer is 'no, this is not supported'. –  bmju Mar 27 at 15:07
    
Got it. Write an SP which copies the user maintenance plan rows (as in the comment above) from the hobbled SQL Server to the non-hobbled one (this needs a linked server set up on one or other side). Call this regularly... from a maintenance plan, for instance. Back up all the maintenance plans on the non-hobbled server. (The only thing to be careful of would probably be to make the names of the plans on each side different, to avoid any confusion.) This is a massive hack, of course. But it actually works. –  bmju 2 days ago

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.