SSRS subscription move to another server

Question

SSRS subscription move to another server

sqlfriend

SSC Guru

Points: 52769
More actions
September 10, 2018 at 10:56 am

#378896

Our servers are SQL 2017.
On our production server and test server we have a folder of reports that has subscriptions.
and we have jobs that scheduled about those subscription.
But once we were testing a build product, it accidentally deletes the subscription on the test server.
Now I want to recover those subscriptions on test server and exactly like what is in production.
I first thought I may do a backup and restore for the reportdb, but on our test server there are more folders, so I don't want to drop them by restore.
And the ssrs reports that has subscription is only in one folder.
Is that possible any tools or methods to transfer only subscriptions of reports in one folder from production to Test server and keep the same subscription ID.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

sqlfriends - Monday, September 10, 2018 10:56 AM
Our servers are SQL 2017.
On our production server and test server we have a folder of reports that has subscriptions.
and we have jobs that scheduled about those subscription.
But once we were testing a build product, it accidentally deletes the subscription on the test server.
Now I want to recover those subscriptions on test server and exactly like what is in production.
I first thought I may do a backup and restore for the reportdb, but on our test server there are more folders, so I don't want to drop them by restore.
And the ssrs reports that has subscription is only in one folder.
Is that possible any tools or methods to transfer only subscriptions of reports in one folder from production to Test server and keep the same subscription ID.
Thanks

The ReportingServicesTools module for Powershell has cmdlets to export and import subscriptions. This article has more information about those cmdlets:
Managing SSRS Subscriptions with PowerShell

Sue

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

I took a look at this link, it seems they copy the subscription to a different path on the same server.

What I need is to copy from one server to another server about subscriptions of reports in a specific folder.

Any other approach? Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 3

sqlfriends - Monday, September 10, 2018 3:41 PM
I took a look at this link, it seems they copy the subscription to a different path on the same server.
What I need is to copy from one server to another server about subscriptions of reports in a specific folder.
Any other approach? Thanks

No it doesn't work that way, You would need to read the article. The import and exports descriptions from the article:

Export-RsSubscriptionXml -- exports subscriptions to disk
Import-RsSubscriptionXml -- imports subscriptions from one or more XML files on disk and recreates a number of the properties to make them valid object types for Set-RsSubscription

There is also an article up here on SSC worth reading, uses Powershell:
â€œDoes that copy subscriptions too?â€ â€“ Now it does! New PowerShell SSRS commands

If you don't want to use Powershell, you could write something using the web service itself:
Accessing the SOAP API

Sue

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Thanks, I looked at the first link you posted http://wragg.io/managing-sql-server-reporting-services-subscriptions-with-powershell/

And I tried to use Import-RsSubscriptionXml with Set-RsSubscription and found Set-RsSubscription no longer has the parameter for path.
You can see in article comments, some one also mentioned this. you can also run get-help set-rssubscription and see there is no path argument any more.
If no path parameter, how can I set to which folder to import in?

Import-RsSubscriptionXml .\MySubscriptions.xml | Set-RsSubscription -Path /Example/Report

Thanks

sqlfriend SSC Guru Points: 52769 More actions · Answer 5

I did a little more explore sees I need to use copy-rsSubscription.

The problem is they all create new subscriptionid when moving to a new server.

I want to keep the same id so that in some of our jobs it refers to the subscription-id.

I want to keep the same on test server and production server so that it saves maintenance headache when doing server upgrade or migration.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

sqlfriends - Tuesday, September 11, 2018 11:45 AM
I did a little more explore sees I need to use copy-rsSubscription.
The problem is they all create new subscriptionid when moving to a new server.
I want to keep the same id so that in some of our jobs it refers to the subscription-id.
I want to keep the same on test server and production server so that it saves maintenance headache when doing server upgrade or migration.

You don't move the jobs. When you create the subscription -
A row is inserted in the Subscriptions table
A row is inserted in the Schedule and ReportSchedule table
A job is created

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Should have added, you can use the copy subscription or the export/import process.

Sue