Wednesday, May 20, 2015

How to restore ReportServer database in SQL Server

We started receiving below errors after performing a report server database restore.

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.


The report server installation is not initialized.(rsReportServerNotActivated)

In short, above errors raise when there is mismatch or different names in report server function between the source and destination servers.

The easiest fix or the best way I prefer doing a report server DB’s restore is:

·         Take backup of ReportServer database of source server.
·         Take backup of Encryption Key of source server. (We need to take backup from Report Server Configuration Manager)
·         Copy both the backups files (ReportServer and Encryption Key) to destination server.
·         Restore the ReportServer DB in destination server.
·         Restore the Encryption Key in destination server.

Note: Remember we don’t need to restore ReportServertempdb in destination server in most of the cases.

Another thing to remember is to change the configuration of data sources and existing reports to point to destination server instead of source server. We have to make these changes manually in report URL after restoring report database.