Move SQL Reporting Services to Another Server

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 SSRS migration steps.

Solution

The high-level SSRS migration steps are:

• 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

Below are the versions and editions I used, however, 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

Even though the steps are the same whether it’s one or 1,000 reports, we’ll keep it simple. Here we’ll use an SSRS Server with one Folder, one Report, and one Subscription.

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

Step 1

Open the Reporting Services Configuration Manager on the new server.

1. Click Stop to stop Reporting Services

Step 2

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

Step 3

Once completed, 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. Obviously a good place to test, but you’ll most likely 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]

Step 4

We need to ensure the logins and users are in sync at this time. To demonstrate what happens if you omit this step, we’ll digress briefly. Go back to the Report Server Configuration Manager.

1. Click Start to start the service

The service starts and it appears to be working. Then this error log appears:

For our example, we’re using the default accounts to run the services on both our source and target SSRS servers. Since there is no associated login with it, the user will not show up in Management Studio. Essentially, it’s orphaned. Regardless, you can still see it by querying sys.sysusers in each database.

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

Even though we aren’t required to delete the orphaned users, we can 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

Step 5

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

Step 6

Stop and start SSRS again.

Step 7

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:

Step 8

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

Step 9

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.

Note: you’ll probably want to disable jobs while testing.

Checklist

In addition to the steps above, 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 a newer version of 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

Below 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)