15

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?

Improve this question

4 Answers 4

Reset to default
10

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.

Improve this answer
1
  • 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.)
    – MikeBeaton
    Commented Mar 30, 2015 at 10:45
4

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.

Improve this answer
0
3

I've been struggling with the EXACT same issue. Here's the main take-away:

No integration services needed on your WEBSERVER. One documented way is to use DTUTIL. Just use ANY SQL Server (even the free developer edition with all the Enterprise features) that has Integration Services installed to copy SQL Server Maintenance Packages from a source to a target--even if it's not the source or target of the package as shown in Example A.

Example A: Run DTUTIL on SQL Server MySSISServerA to copy a SQL Maintenance Package from MySourceServerB to MyDestServerC.

DTUTIL /SQL "Maintenance Plans\Nightly Maintenance" /copy  sql;"Maintenance Plans\Nightly Maintenance" /sourceserver MySourceServerB /destserver MyDestServerC /Q
Improve this answer
2
  • With the caveat that ANY = same version, otherwise, DTUTIL will upgrade the plan to vCurrent
    – billinkc
    Commented Mar 1, 2017 at 21:24
  • Great point! But upgrades can be helpful--for what we do anyway. What I like to do is create plans in SQL 2008 and copy/upgrade those plans to SQL 2008, 2008 R2, 2012,2014, 2016.
    – Sting
    Commented Mar 2, 2017 at 15:03
0

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.

Improve this answer
1
  • The question stated "without using Integration services"
    – AndreaCi
    Commented May 17, 2021 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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