Gotcha on named instance migration for SSRS



Gotcha on named instance migration for SSRS 


You have to migrate a SSRS named instance to a new server in parallel with the existing SSRS Server.    Not an uncommon scenario when in production and bringing up a new server, test and validate the new environment, and need to minimize potential downtime.  So you bring up the new server, backup the SSRS repository databases, and restore on the new server, assign the current service account to new service, restore the encryption key, and then Report Manager failed on an internal error.   You get an error message:  “An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help Invalid object name ‘ReportServerTempDB.dbo.TempCatalog‘. Could not use view or function ‘ExtendedCatalog’ because of binding errors”.   What happened?    When installed, SSRS will actually hardcode the name of the ReportServerTempDB database to stored procedures and functions in the ReportServer database.   The version of SSRS being used does not matter.   Approximately 72 stored procedures and 2 functions have a hard coded reference embedded internally in SSRS.    Not a good feeling when at a client and then had multiple named instances being migrated in each DEV, TEST, and PROD environments.            


Here is a query against the metadata view to see the full impact:
USE [ReportServer]
GO
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%reportservertempdb%' 
GO


Here is a code snippet from the stored procedure [dbo].[AddDataSet] :
IF(@EditSessionID is not null)
BEGIN
    INSERT
        INTO [ReportServerTempDB].dbo.TempDataSets
            (ID, ItemID, [Name], LinkID)
        VALUES
            (@ID, @ItemID, @Name, @ActualLinkID)
    …


To fix, you have to modify the reference.  Script out all the stored procedures and functions from the migrated database with a drop and create, perform a global replace the current to the proper name of the ReportServerTempDB database, and then execute the script.    Restart the service and you should be fine.   

Reference: http://sql-articles.com/reporting-services/how-to-rename-your-existing-report-server-database/