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:

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?
LVL 18
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLAuthor 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 VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
dbaSQLAuthor Commented:
Wonderful, Nakul.  You mean the Agent only needs to be restarted.  Yes?
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

dbaSQLAuthor 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.
dbaSQLAuthor 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)
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

   , e.path
   , d.description
   , a.SubscriptionID
   , laststatus
   , eventtype
   , LastRunTime
   , date_created
   , date_modified
   ReportServer.dbo.ReportSchedule a
   JOIN msdb.dbo.sysjobs b ON a.ScheduleID =
   JOIN ReportServer.dbo.ReportSchedule c ON = c.ScheduleID
   JOIN ReportServer.dbo.Subscriptions d  ON c.SubscriptionID = d.SubscriptionID
   JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE = 'yourReport'
dbaSQLAuthor 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?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
if old and new SSRS DB on the same Sql server

you may get duplicated subscriptions jobs can check if subscription of you report is running going to , for example, http://yourSSRS/reports/... manage subscription..
2. you can check if sql agent  SSRS jobs name, for example, like this one ran -- check job(s) history


3. contact "subscribers" to check if they got "subscription" email, etc

4. you can run this report
use ReportServer new 
SELECT c.path as ReportPath
--Need to convert Subscription ID to a varchar to allow it to be used in our Part 2 report
,CONVERT(varchar(36),s.subscriptionID) as SubscriptionID
--Differentiate between email subscriptions and subscriptions that drop reports out onto a file share
,case s.DeliveryExtension
when 'Report Server Email' then 'Email'
when 'Report Server FileShare' then 'File Drop'
end as SubscriptionType
--Keep track of which subscriptions are using shared schedules and which are using their own custom schedule
,case sch.Type
when 0 then sch.Name
else '(Custom)' end as SubscriptionScheduleName
--IsDataDriven is another part of the querystring for the Report Manager URL.
--If the subscription has any data settings, it's a data-driven subscription
when DATALENGTH(s.DataSettings) IS NULL then 'False'
else 'True' End As IsDataDriven
--While these fields are NTEXT instead of XML, they contain XML and can be queried via the .value function
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="TO"]/Value)[1]','VARCHAR(1024)') AS [SendTo]
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="CC"]/Value)[1]','VARCHAR(1024)') AS [SendCC]
--Depending on the number of parameters for the report, you may wish to add or remove these
--If some reports have no parameters, columns will be
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[1]','varchar(max)') as Parameter1
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[2]','varchar(max)') as Parameter2
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[3]','varchar(max)') as Parameter3
, s.laststatus as SubscriptionStatus
, s.LastRunTime
--Determine what subscriptions haven't been run in the last month. Perhaps they can be deleted?
, case DateDiff(month,GetDate(), s.LastRunTime)
when 0 then 'Y'
when -1 then 'Y'
else 'N' end as RunInLast30Days
, s.ModifiedDate
FROM ReportServer.dbo.Subscriptions s
INNER JOIN ReportServer.dbo.Catalog c
ON s.report_oid = c.itemid
INNER JOIN ReportServer.dbo.ReportSchedule rsc
on rsc.subscriptionID = s.SubscriptionID
and rsc.ReportID = c.itemID
INNER JOIN ReportServer.dbo.Schedule sch
on sch.scheduleID = rsc.scheduleID
--WHERE LOWER(c.path) like '/Manufacturing%'

Open in new window

Monitor Reporting Services Subscriptions

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor 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 link.  Very useful information there.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.