Migrating SSRS to 2016 - Subscriptions and Eliminating Errors in the Error Log

Migrating from SQL Reporting Services 2012 to 2016 is a fairly straightforward process that doesn't require a lot of attention here. What I do want to talk about is SSRS subscriptions. When you migrate an SSRS instance from one server to another, unless you also restore msdb those subscription jobs won't be coming with you. The subscriptions still exist in your ReportServer database and because the subscriptions are still in the ReportServer database and not in msdb, SSRS starts recording errors to the SSRS error logs to the tune of around 32MB a minute worth of logs. This happens because SSRS is looking for the Agent job for a subscription in the ReportServer database but it's not there so it logs an error. And it will keep checking over and over and over (Microsoft will likely change this behavior, we can hope). So how do we keep our logs in check and fix our subscriptions? Just run the following script:

DECLARE 
   @ownerLogin VARCHAR(100)
  ,@scheduleId VARCHAR(36)

SET @ownerLogin=N'MyDomain\ssrsAdmin' --SSRS Service Account which is also an admin in Report Manager

DECLARE JobCreateCursor CURSOR STATIC FORWARD_ONLY
FOR
SELECT CAST(ScheduleID AS VARCHAR(36)) AS ScheduleId 
FROM schedule

OPEN JobCreateCursor
FETCH NEXT FROM JobCreateCursor INTO @scheduleId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_add_job @job_name = @scheduleId,@owner_login_name = @ownerLogin

FETCH NEXT FROM JobCreateCursor INTO @scheduleId

END

CLOSE JobCreateCursor
DEALLOCATE JobCreateCursor

Notice we aren't creating any job steps here, that's because SSRS will do that for us. Once you create the Agent jobs SSRS goes and validates those jobs and adds the steps as part of the subscriptions. If you try to go to the properties of one of the newly created jobs you will get an error until SSRS runs it's linking process, then you will be able to view the jobs and the subscription will run as it did on the old server. That and you won't be filling up the drive with SSRS error logs.