SSRS – Migrating or Restoring Reporting Services Databases

Posted on Author

Background:

This post topic is one that I look for often, since Reporting Services stores data for connection strings and settings encrypted, you need to take some steps when restoring the databases either to a new server in the case of migration or to a Test / Dev environment in the case of an environment refresh where you want all the same rights, users, reports for testing or application functionality.

Setup and Code:

  1. Backup the ReportServer and ReportServerTenpDB on the source server
  2. From the Reporting Services Configuration Console on the source server go to the EncryptionKeys node on the left navigation and choose Backup.
  3. Save the key to disk and copy it to your destination server.
  4. On the destination server stop the Reporting Services SQL Service from the Surface Area Configuration Manager
  5. Restore the backup of ReportServer and ReportServerTempDB to the destination server.
  6. On the Destination Server restart the SQL Server Reporting Services Service from Surface Area Configuration Manager.
  7. Connect to the Reporting Services Configuration Manager on the destination server and choose Database node from the left navigation.
  8. Choose Change Database > Choose Existing report server database.
  9.  Select the restored database called ReportServer on the destination instance.
  10. From the left navigation choose Encryption keys, then choose Restore.
  11. Restore the encryption key on the destination from the file you copied in step 3.
  12. From SSMS on the destination server run a select * from ReportServer.dbo.Keys. Here you are looking for a row where the InstanceName column is the name of the Source Server. Delete this one row. If you miss this step when you browse to the reporting services home page it will throw an error that “Scale Out deployment is not supported by this version of reporting services”
  13. Browse to the Reporting Services home page http://destinationserver/reportserver/pages/folder.aspx. The page should load normally with all reports / folders available.

Resolution:

This article discusses how to migrate reporting services to a new server instance while keeping all reports / datasources and users intact.

Information on Terms:

SSMS – Sql Management Studio

SSRS – Sql Server Reporting Services , Microsoft’s Report publishing Platform

Subject Links:

https://techblog.xavient.com/sql-server-reporting-services-migration/