Move SQL Reporting Services to Another Server

By: Joe Gavin   |   Updated: 2020-02-25   |   Comments (20)   |   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.

And here's the Subscription that automatically runs MyReport.

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

Target Server

Open the Reporting Services Configuration Manager on the new server.

1. Click Stop to stop Reporting Services

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

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

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.

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

You should see the restore was successful and Reporting Services was 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

DELETE [dbo].[Keys]
WHERE InstanceName LIKE 'SQL2012'
GO

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

And here are the Subscriptions.

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

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

• SQL Server Reporting Services Migration Tips
• Migrate a Reporting Services Installation (Native Mode)

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips