Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >

How to Migrate a Report Services database to a new SQL server

11 April,19 at 11:51 AM

This article walks through the steps to back up and migrate a Report Services database to a new server.


In most instances, because the data in the Report Services database is not live data, it is easier to rerun the Report Services Installer, do a fresh install of Report Services, create a new db instance on a new SQL server, and then resync the data.


In the rare occurrence that a new database cannot be installed and resynced, below are the steps that can be used to back up, migrate, and restore the Report Services to a new SQL server.



Step 1: Back up the Report Services database


Before backing up the Report Services database, please stop Report Services to prevent the backup from being corrupted.


a) Launch SQL Server Management Studio


b) Expand Databases in Object Explorer


c) Right click the database Report Services is using > Tasks > Back up...




d) Under the Destination section in Back up to, select Disk


e) Click Add… > Select File name > Select the folder where backup file will be saved




f) Click OK



Step 2: Restore the Report Services database backup


a) Launch SQL Server Management Studio


b) Connect to the server where the database will be restored to


c) Right click Databases in Object Explorer > select Restore Files and Filegroups…




d) Input database name in To database > Select From device and select the database backup file > Select restore source




e) Click OK


For more information on how to back up and restore databases, please refer to the below link from Microsoft (



Step 3: Reconnect Report Services to the new database


a) Launch Report Service Control Panel > stop the service > Close Report Service Control Panel




b) Launch Registry Editor


c) Navigate to HKEY_LOCAL_MACHINE/SOFTWARE/Centrify/Report Services/Service


d) Change DatabaseName and ServerName to the new database name and database server




e) Launch Report Service Control Panel again


f) Go to Troubleshooting tab > click Validate > click Yes


This step is to check whether the service account of Report Services has enough permission or not


g) If the error message on the below following screenshot is displayed, please follow the step 4. If not, please skip to step 5.





Step 4: Grant some database permissions to the service account of Report Services


a) Launch SQL Server Management Studio


b) Connect to the new database server


c) Navigate to Security > right click Logins > select New Login…




d) Input the service account of Report Services in Login name in General page





e) Click User Mapping > Select that new database in Users mapped to this login > Select db_ddladmin, public and SnapshotServices in Database role membership





f) Click OK


g) Launch Report Services Control Panel > start service



Step 5: Reconnect the data source of reports to the new database


a) Launch Report Services



b) Click ReportDataSource


c) Change that connection string to the new database




d) Click Test Connection to check whether the connection works or not.




e) If the error in the above screenshot occurs, the root cause is Kerberos double-hop. For example, the user logged on the SSRS (SQL Server Reporting Services) Report Manager from a machine which is not the one where the SSRS is deployed, there is a 2-hop scenario.


The best solution to solve this problem is to select Use as Windows credentials when connecting to the data source and provide a credential which has ‘SnapshotViewer’ role on the Centrify Report Services’ database.


f) Click Apply