We help IT Professionals succeed at work.

How to confirm SSRS Subscriptions are good after restoring ReportServer to another database

188 Views
Last Modified: 2018-08-30
ReportServer Subscriptions -- We migrated ReportServer to ReportServerNEW with a simple backup/restore -- on the SAME server.  When we restored ReportServerNEW, we also used the encryption key from the source ReportServer instance, and all reports are accessible now in the new SSRS instance, Report Manager.  

But -- what about the subscriptions?  How do I know they are good?  They may be fine, because I used the steps outlined here for the move:
   https://docs.microsoft.com/en-us/sql/reporting-services/report-server/moving-the-report-server-databases-to-another-computer-ssrs-native-mode?view=sql-server-2017

WHERE it says:    "Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes."

I just don't know how to confirm.  What is the right way to confirm that the subscriptions are all scheduled/enabled/on in the new instance, after migrating the ReportServer database?
Comment
Watch Question

Author

Commented:
Are there any SSRS Experts available to answer this question?  I just restored ReportServer to ReportServerNEW, same server.  The encryption key was used, the SSRS instance re-initialized and all reports are available in the new ReportManager as expected.

I need to confirm if the SSRS subscription schedules are good?  Are there any changes necessary to re-align the SSRS Subscription Agent jobs with the new database, after the restore?

Super, super critical.  Any input is hugely appreciated.
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
The subscription jobs will need to be recreated. As part of the migration, did you restart the SQL Server Agent after the databases were restored? If yes, your subscription jobs under the Agent would be pointing to the correct ReportServerNEW database and you should be all set.

Author

Commented:
Wonderful, Nakul.  You mean the Agent only needs to be restarted.  Yes?

Author

Commented:
Nakul, I must question what you have said.  Remember, I restored ReportServer to ReportServerNEW, on the same SQL Server.  If the old ReportServer database is still there, why would the subs be pointing to the new database?  This is what I did:

I backed up ReportServer
Restored it to ReportServerNEW, with encryption key from ReportServer -- same SQL Server
Reinitalized SSRS via Reporting Svcs Configuration Mgr
Reporting Services for the old database is stopped/disabled.
Reporting Services for the new database is enabled/running.
All reports are available via Report Manager

If all Agent SUB jobs were targeting ReportServer before, why does an Agent restart point them to ReportServerNEW ?

Please clarify.

Author

Commented:
Nakul, or any other SSRS Expert - can you answer my question?  What in these steps that I've outlined redirects the Agent subscription jobs from ReportServer to ReportServerNEW ?  

I backed up ReportServer
Restored it to ReportServerNEW, with encryption key from ReportServer -- same SQL Server
Reinitalized SSRS via Reporting Svcs Configuration Mgr
Reporting Services for the old database is stopped/disabled.
Reporting Services for the new database is enabled/running.
All reports are available via Report Manager
SQL Server Agent service was restarted (after that last post from Nakul)
EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
normally Sql agent jobs will be "auto" created  "GUI numbers"

you'd need to disable  old ssRs sql server subs job

from SSRS config -- point to the new SSRS  Sql server DBS
open report subscription to see "should be executed " subscription status " eeror -success

or just start job
after you know what report

try this script to check what job is for your report

SELECT
     b.name AS JobName
   , e.name
   , e.path
   , d.description
   , a.SubscriptionID
   , laststatus
   , eventtype
   , LastRunTime
   , date_created
   , date_modified
FROM
   ReportServer.dbo.ReportSchedule a
   JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.name
   JOIN ReportServer.dbo.ReportSchedule c ON b.name = c.ScheduleID
   JOIN ReportServer.dbo.Subscriptions d  ON c.SubscriptionID = d.SubscriptionID
   JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE
   e.name = 'yourReport'

Author

Commented:
Thank you, Eugene.  

>>you'd need to disable  old ssRs sql server subs job
I did not disable the old SSRS sub jobs

>>from SSRS config -- point to the new SSRS  Sql server DBS
I did not have to do this either, because two SSRS instances were enabled;  one for ReportServer and one for ReportServerNEW.  This was before me, and unfortunately, I did not go in before today to see which SSRS sub jobs were for which SSRS instance.  

I DID go into SSRS config and shut down the old SSRS instance, which is pointing to ReportServer.  Now I just need to be able to confirm that what remains pertains to the new SSRS instance, is scheduled and will run as expected.

I've got 126 reports on this instance and a group of people who are looking for me to say all subscriptions are scheduled as expected, before tomorrow morning, when they will be expecting them to be running.  Can you help me do that?
SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Eugene.  Please forgive the delay.   I have  not heard one complaint from any of the report / subscription recipients.  I have also run your 4th suggestion and can see the the Subscriptions.LastRunTime is current -- so they are running actively, and again, I haven't received any complaints.   So I think we're good.  Thank you very much for your #4 query as well as the https://ginameronek.com link.  Very useful information there.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.