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

3 Answers 3

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

Check this link Maintenance Plans from One Server to other

share|improve this answer
5  
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

@Fulproof:

Based on what @RicardoBalda says (which all works as advertised), it seems that some significant chunk of SSIS must be 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.)

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
    
@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 2 days ago
    
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 yesterday
    
Final point - I hope - I'm not sure there's any way to re-import exported plans, except by (installing and) using the SSIS front-end? Also, dtutil (as mentioned in the answer with the highest upvotes) is not present on my SQL Server Web edition, without SSIS; so I think it would need to be installed (as part of SSIS, or some other way) to get at this solution anyway? –  bmju yesterday
    
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 yesterday

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.