Move SQL Reporting Services to Another Server


By:   |   Updated: 2020-02-25   |   Comments (1)   |   Related: > Reporting Services Administration


Problem

You need to migrate a SQL Server Reporting Services (SSRS) Native Mode environment installed on one server and move it all to another server. Check out this tip to learn the steps.

Solution

Let's walk through the steps.

The high-level overview is:

  • Backup SSRS databases on source server
  • Backup Encryption Key on source server
  • Restore SSRS databases on target server
  • Restore Encryption Key on target server
  • Remove old server name from the Keys table on the target server
  • Test

These are the versions and editions I used, but you can follow these steps for other versions and editions.

  • Windows 10
  • SQL Server 2012 Developer Edition (source)
  • SQL Server 2017 Developer Edition (target)

Source Server

We'll use a very simple example of an SSRS Server with one Folder, one Report and one Subscription. The steps are the same whether it's one or one-thousand reports so we'll keep it simple.

Here's the MyReports Folder with MyReport.

Source SSRS Server Report

And here's the Subscription that automatically runs MyReport.

Source SSRS Server Report Subscription

Edit the directory you want to use for your backup files here and execute to backup databases ReportServer and ReportServerTempDB.

-- backup ReportServer
BACKUP DATABASE [ReportServer] 
TO  DISK = N'C:\backups\ReportServer_migration.bak' -- edit backup directory and file name
WITH INIT,  
NAME = N'ReportServer-Full Database Backup', 
SKIP, 
COMPRESSION,  
STATS = 10
GO
 
-- backup ReportServerTempDB
BACKUP DATABASE [ReportServerTempDB] 
TO  DISK = N'C:\backups\ReportServerTempDB_migration.bak' -- edit backup directory and file name
WITH INIT,  
NAME = N'ReportServerTempDB-Full Database Backup', 
SKIP, 
COMPRESSION,  
STATS = 10
GO

Backup the encryption key by opening the Reporting Services Configuration Manager on the source server

  1. Encryption Keys
  2. Backup
  3. Enter directory and file name
  4. Give it a password that meets your domain requirement
  5. Confirm password
  6. Click OK
Backup Encryption Key

Target Server

Open the Reporting Services Configuration Manager on the new server.

  1. Click Stop to stop Reporting Services
Stop SSRS

Copy the backup files from the source to the target server.

Edit the following code with the path to your backup files, and path to the physical database files in the following and execute to restore the two databases. (Note, everything is pointing to C:\ here as I'm testing this on one laptop with 2 named instances of SQL Server. Good place to test, but you'll most like be doing this on actual servers.)

-- Restore ReportServer
USE [master]
RESTORE DATABASE [ReportServer] 
FROM DISK = N'C:\backups\ReportServer_migration.bak' -- edit backup directory and file name
WITH  FILE = 1,  
-- edit physical file path
MOVE N'ReportServer' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ReportServer.mdf',  
MOVE N'ReportServer_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ReportServer_log.ldf',  
NOUNLOAD,  
REPLACE,  
STATS = 5
GO
 
-- update compat level
ALTER DATABASE [ReportServer] SET COMPATIBILITY_LEVEL = 140
GO
 
-- set db owner to sa
USE [ReportServer]
GO
ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa]
GO
 
-- dbcc
dbcc CHECKDB([ReportServer]) WITH NO_INFOMSGS
 
-- Restore ReportServerTempDB
USE [master]
RESTORE DATABASE [ReportServerTempDB] 
FROM DISK = N'C:\backups\ReportServerTempDB_migration.bak' -- edit backup directory and file name
WITH  FILE = 1,  
-- edit physical file path
MOVE N'ReportServerTempDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ReportServerTempDB.mdf',  
MOVE N'ReportServerTempDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ReportServerTempDB_log.ldf',  
NOUNLOAD,  
REPLACE,  
STATS = 5
GO
 
-- update compat level
ALTER DATABASE [ReportServerTempDB] SET COMPATIBILITY_LEVEL = 140
GO
 
-- set db owner to sa
USE [ReportServerTempDB]
GO
ALTER AUTHORIZATION ON DATABASE::[ReportServerTempDB] TO [sa]
GO
 
-- dbcc
dbcc CHECKDB([ReportServerTempDB]) WITH NO_INFOMSGS
  
-- sp_helpdb
EXEC sp_helpdb [ReportServer]
EXEC sp_helpdb [ReportServerTempDB]

At this point we need to be sure the logins and users are in sync, but we'll digress briefly to show what happens if you omit this step. Go back to the Report Server Configuration Manager.

  1. Click Start to start the service
Start SSRS

The service starts and it appears to be working, but we have this is the error log:

SQL Server errorlog

For our example we're using the default accounts to run the services on both our source and target SSRS servers. The user will not show up in Management Studio because there is no associated login with it so it's essentially orphaned but you can see it by querying sys.sysusers in each database.

SELECT name FROM [ReportServer].[sys].[sysusers]
SELECT name FROM [ReportServerTempDB].[sys].[sysusers]

We don't really have to delete the orphaned users, but let's do some housekeeping while we have the chance.

USE [ReportServer]
GO
DROP SCHEMA [NT SERVICE\ReportServer$SQL2012]
GO
DROP USER [NT SERVICE\ReportServer$SQL2012]
GO
 
USE [ReportServerTempDB]
GO
DROP SCHEMA [NT SERVICE\ReportServer$SQL2012]
GO
DROP USER [NT SERVICE\ReportServer$SQL2012]
GO

Now we're ready to add our users and assign them to the proper groups.

-- master
USE [master]
GO
CREATE USER [NT SERVICE\SQLServerReportingServices] 
FOR LOGIN [NT SERVICE\SQLServerReportingServices] 
WITH DEFAULT_SCHEMA=[NT SERVICE\SQLServerReportingServices]
GO
 
ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
 
-- msdb
USE [msdb]
GO
ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
 
-- ReportServer
USE [ReportServer]
GO
ALTER ROLE [db_owner] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
 
-- ReportServerTempDB
USE [ReportServerTempDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO
ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices]
GO

Start and stop SSRS again.

Check for errors in the error log and there should not be any more login failures.

EXEC sp_readerrorlog

Look for NT SERVICE\SQLServerReportingServices connections.

EXEC sp_who 'NT SERVICE\SQLServerReportingServices'

And we expect to see something like this.

sp_who

Go back to the Report Server Configuration Manager.

  1. Encryption Keys
  2. Restore
  3. Fully qualified file name of Encryption Key backup from the source server
  4. Password
  5. Click OK
Restore Encryption Key

You should see the restore was successful and Reporting Services was restarted.

Encryption Key restored and SSRS restarted

If you're on SQL Server Standard Edition, you'll encounter a "scale-out deployment is not supported in this edition of reporting services" error when you open the SSRS Web Portal.

We just need to delete the source server record.

SELECT * 
FROM [ReportServer].[dbo].[keys]
GO
[ReportServer].[dbo].[keys]
DELETE [dbo].[Keys]
WHERE InstanceName LIKE 'SQL2012'
GO

Open http://targetservername/Reports/ and here are our Reports.

Target SSRS Server Report

And here are the Subscriptions.

Target SSRS Server Report Subscription

And here is the SQL Agent Job that was automatically created.

SQL Agent Job

You'll probably want to disable jobs while testing.

Checklist

Also, here's a handy checklist to use if you like simple checklists like I do.

SSRS Migration Checklist

  • Source Server
    • Backup ReportServer and ReportServerTempDB together
    • Copy backups to target server
    • Backup SSRS Encryption Key
  • Target Server
    • Stop Reporting Services thru Reporting Services Configuration Manager
    • Restore ReportServer and ReportServerTempDB from backups
    • Update Compatibility Level if going to newer version SQL Server
    • Update database owners
    • Run DBCC CHECKDB on ReportServer and ReportServerTempDB
    • Fix logins and users
    • Start Reporting Services
    • Verify no login errors in errorlog
    • Restore Encryption Key
    • DELETE [ReportServer].[dbo].[Keys]  WHERE InstanceName = 'SourceSsrsServerName'
    • Validate at http://TargetServerName/Reports/
    • Test
Next Steps

Following are some links to more info on migrating SQL Server Reporting Services



Last Updated: 2020-02-25


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips





Comments For This Article




Tuesday, February 25, 2020 - 5:15:54 PM - Mustafa ELmasry Back To Top (84797)

It is good solution in case we are moving one server to new server but let us assume that we have multiple reporting services and we need to consolidate all of them in one server using one DB report server and report server temp at this time this solution will not be applicable and I think we should move the reports RDL only from the multiple servers to the new server using tool like RSScripter.

but based on my experience this tool migrating only the reports and the data source will be created without the connection information so you should take care about the Data source connection information and the security also you need to move it manual . is there is any other solution to move all the reports information (RDL, Connection string , security , emails ) from multiple reporting servers to one report server?



download





Recommended Reading

SQL Server Reporting Services (SSRS) Log Files for Troubleshooting

Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

PowerShell Commands for SQL Server Reporting Services

SQL Server Reporting Services Rest API

SSRS ReportServer Database Overview and Queries








get free sql tips
agree to terms