Link to home
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

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

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?
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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.
Avatar of Nakul Vachhrajani
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.
Avatar of dbaSQL

ASKER

Wonderful, Nakul.  You mean the Agent only needs to be restarted.  Yes?
Avatar of dbaSQL

ASKER

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.
Avatar of dbaSQL

ASKER

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)
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'
Avatar of dbaSQL

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of dbaSQL

ASKER

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.