SSRS Report Migration

 

Migrate SQL Server Reporting Services (SSRS)

---

Abstract

This document presents a structured overview of the available approaches for migrating SQL Server Reporting Services (SSRS) from an existing environment to a target server. It highlights best practices, explains the technical workflow involved in each method, and discusses key considerations and challenges associated with SSRS migration. The objective is to provide a reliable reference for database administrators and BI engineers managing enterprise reporting infrastructure.

---

Migration Approaches

Four primary approaches are commonly used when migrating SSRS workloads:

1. Manual step-by-step migration of reports and configurations

2. Migration using Visual Studio (SSDT) by loading and redeploying RDL files

3. Automated migration using the rs.exe scripting utility

4. Migration via third-party tools

Each of these is discussed in detail below, along with associated advantages and limitations.

---

Approach 1: Manual Step-by-Step Migration

This is the most controlled method and is commonly used in environments that require precise validation of content, folder structure, permissions, and configurations.

Advantages

• Ensures all reports are deployed accurately to their intended folders.

• Offers high visibility and control throughout the migration process.

Disadvantages

• Time-consuming, especially for large SSRS ecosystems.

---

High-Level Workflow

1. Backup SSRS Databases (Source Server)

Both SSRS databases must be backed up:

• ReportServer

• ReportServerTempDB

-- Backup ReportServer
BACKUP DATABASE [ReportServer]
TO DISK = N'C:\backups\ReportServer_migration.bak'
WITH INIT, NAME = N'ReportServer-Full Database Backup',
SKIP, COMPRESSION, STATS = 10;

-- Backup ReportServerTempDB
BACKUP DATABASE [ReportServerTempDB]
TO DISK = N'C:\backups\ReportServerTempDB_migration.bak'
WITH INIT, NAME = N'ReportServerTempDB-Full Database Backup',
SKIP, COMPRESSION, STATS = 10;

---

2. Backup SSRS Encryption Key

Using the Reporting Services Configuration Manager:

1. Open Encryption Keys

2. Click Backup

3. Provide a backup location

4. Set a password

5. Confirm and complete the backup

---

Restore on Target Server

1. Stop Reporting Services

Using SSRS Configuration Manager → Stop the service.

2. Restore SSRS Databases

Restore both ReportServer and ReportServerTempDB databases to the target SQL instance.

RESTORE DATABASE [ReportServer]
FROM DISK = N'C:\backups\ReportServer_migration.bak'
WITH MOVE 'ReportServer' TO '...\ReportServer.mdf',
MOVE 'ReportServer_log' TO '...\ReportServer_log.ldf',
REPLACE, STATS = 5;

ALTER DATABASE [ReportServer] SET COMPATIBILITY_LEVEL = 140;
ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];

DBCC CHECKDB([ReportServer]) WITH NO_INFOMSGS;

Repeat for the TempDB.

---

Synchronize Users and Fix Orphan Accounts

Query users:

SELECT name FROM ReportServer.sys.sysusers;
SELECT name FROM ReportServerTempDB.sys.sysusers;

Remove outdated service accounts:

DROP SCHEMA [NT SERVICE\ReportServer$SQL2012];
DROP USER   [NT SERVICE\ReportServer$SQL2012];

Add required service identities and assign roles on the target:

CREATE USER [NT SERVICE\SQLServerReportingServices]
FOR LOGIN [NT SERVICE\SQLServerReportingServices];

ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices];
ALTER ROLE [db_owner]   ADD MEMBER [NT SERVICE\SQLServerReportingServices];

---

Restore Encryption Key

After database restoration, restore the encryption key:

1. Open Encryption Keys

2. Select Restore

3. Provide key backup file and password

---

Fix “Scale-Out Deployment Not Supported” Error

Delete old instance entries:

DELETE FROM [ReportServer].[dbo].[Keys]
WHERE InstanceName LIKE 'SQL2012';

---

Final Validation

• Start SSRS

• Open the Web Portal

• Validate folder hierarchy, reports, and subscriptions

• Execute smoke tests on all critical reports

---

Approach 2: Deploying Reports Using Visual Studio (SSDT)

Advantages

• Clean deployment structure with ability to reorganize content

• Easy elimination of outdated or unused reports

• Enables proper source control integration (Azure DevOps, GitHub, etc.)

Disadvantages

• Requires more manual steps for configuration

• Time-intensive for large environments

---

Steps

1. Backup Deployed Reports via rs.exe Script

Run the backup script:

rs -s http://localhost/reportserver 
   -i D:\Scripts\Backup_Reports.rss 
   -e Mgmt2010 
   -v backupFolder="D:\Scripts\BackupReports" 
   -v parentFolder=""

2. Create Visual Studio Solution

• Create one project per report folder

• Import RDL files

• Configure shared data sources

• Configure shared datasets

3. Use Azure DevOps for Source Control

Version-control RDL files, data sources, and data sets.

4. Configure Deployment Settings

Using Configuration Manager → set target server URL, folder paths.

5. Build and Deploy

Deploy either full solution or individual reports.

---

Approach 3: Migration Using rs.exe Utility

This is the fastest automated option but requires scripting knowledge.

Advantages

• Very fast for bulk migration

• Repeatable and script-friendly

Disadvantages

• Complex for beginners

• Requires careful credential and parameter handling

---

Example rs.exe Command

rs.exe -i ssrs_migration.rss -e Mgmt2005 
      -s http://MyServer/ReportServer2008 
      -u MyDomain\Reports -p MyPassword 
      -v ts="http://MyServer/ReportServer2012" 
      -v tu="MyDomain\Reports2012" 
      -v tp="MyPassword"

---

Approach 4: Migration Using Third-Party Tools

Common tools include:

1. Reporting Services Scripter

Useful for:

• Extracting folder structures

• Extracting RDL files

• Migrating subscriptions

Documentation:
https://www.mssqltips.com/sqlservertip/2627/migrating-sql-reporting-services-to-a-new-server/

---

References

1. Microsoft – SSRS Migration Guide

2. Moving SSRS Databases to Another Server

3. RS.exe Utility Documentation

4. SSRS Migration Sample Scripts (GitHub)

5. SQLShack – Low-Risk Database Migration Practices

6. Reporting Services Backward Compatibility

7. SSRS Configuration Tool Overview

---

🎯 Your Article is Now R&D-Ready

This rewritten version:

✔ Reads like a technical document

✔ Showcases real enterprise SQL Server domain expertise

✔ Fits perfectly into your Documentation Portal theme

✔ Provides clean structure, easy navigation, and strong clarity

✔ Demonstrates practical SSRS migration knowledge for career/portfolio use

Read More