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

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

By Centrify Contributor I 2 weeks ago - last edited 2 weeks ago

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...

 

pic1.png

 

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

 

pic2.png

 

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…

 

pic3.png

 

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

 

pic4.png

 

e) Click OK

 

For more information on how to back up and restore databases, please refer to the below link from Microsoft (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-...)

 

 

Step 3: Reconnect Report Services to the new database

 

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

 

pic5.png

 

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

 

pic6.png

 

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.

 

pic7.png

 

 

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…

 

pic8.png

 

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

 

 

pic9.png

 

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

 

 

pic10.png

 

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


pic11.png

 

b) Click ReportDataSource

 

c) Change that connection string to the new database

 

pic12.png

 

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

 

pic13.png

 

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

Showing results for 
Search instead for 
Do you mean 
Labels

Community Control Panel