Skip to main content
Incorporated comment
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

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.

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.

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

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.

Source Link

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