Migrating Your SSRS ReportServer Database

Migrating an SSRS ReportServer database is deceptively simple. You copy the .bak file, run RESTORE, and expect everything to work… or so I thought. Without careful handling of encryption keys, logins, and service‐account mappings, you may end up with a database that refuses to decrypt connection strings or throws permission errors in the portal.

The encryption key is the one that will actually stop you. Every stored credential and connection string in the ReportServer database is encrypted with a key that lives on the source server, not in the backup. Restore the database without it and the reports are all there in the portal, and none of them can connect to anything. Export the key first, before you touch a backup, because if the source server is gone by the time you notice, those credentials are gone with it.

Export the Encryption Key

Before creating or copying any database backups, export the SSRS encryption key from the source server. In Report Server Configuration Manager, navigate to Encryption Keys, select Backup, and choose a secure path for the .snk file. Assign a strong password and store both the file and password in a secure location.

Back Up the ReportServer Database

With the encryption key secured, perform a full backup of the ReportServer database:

BACKUP DATABASE [ReportServer]
  TO DISK = N'D:\Backups\ReportServer_FULL.bak'
  WITH FORMAT, COMPRESSION, STATS = 10;

Verify the backup completes successfully and copy the resulting .bak file to the destination server’s backup folder. Also double-check that the destination instance has its own fresh ReportServer backup in case you need to roll back.

There’s a second database, ReportServerTempDB, and I skip backing it up. It holds session and cached report data rather than anything you’d miss, and SSRS recreates what it needs. It does have to exist on the destination though, with the same permissions, which is why it shows up in the grants further down. If you’re moving to a server where SSRS was never configured, get SSRS to create both databases first through Report Server Configuration Manager, then restore over the top of ReportServer.

Restore the Database on the Destination

I always switch the ReportServer database into single-user mode to prevent connection conflicts:

USE [master];
ALTER DATABASE [ReportServer] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Restore the database, overwriting any existing copy:

RESTORE DATABASE [ReportServer]
  FROM DISK = N'N:\Backups\ReportServer_FULL.bak'
  WITH REPLACE, STATS = 5;
GO

Return the database to multi-user mode:

ALTER DATABASE [ReportServer] SET MULTI_USER;
GO

Immediately check sys.databases or the SQL Server error log to ensure there were no lock or permission errors during the restore.

Reassign Database Owner and Fix Logins

SSRS requires sa as the database owner. Execute:

USE [ReportServer];
ALTER AUTHORIZATION ON DATABASE::ReportServer TO sa;
GO

Next, remap any orphaned users whose SIDs no longer match. For example:

EXEC sp_change_users_login 'Auto_Fix', 'YourUser';

Validate that each database user now correctly maps to its server login.

Recreate the SSRS Service Account User

The SSRS service account (commonly RptSrvc) must exist inside ReportServer and belong to the RSExecRole:

-- 1. Create the service‐account login (if it doesn’t already exist)
CREATE LOGIN [DOMAIN\RptSrvc] FROM WINDOWS;
GO

-- 2. Grant rights in the ReportServer database
USE [ReportServer];
GO
CREATE USER [DOMAIN\RptSrvc] FOR LOGIN [DOMAIN\RptSrvc];
GO
EXEC sp_addrolemember 'RSExecRole', 'DOMAIN\RptSrvc';
GO

-- 3. Grant rights in the ReportServerTempDB database
USE [ReportServerTempDB];
GO
CREATE USER [DOMAIN\RptSrvc] FOR LOGIN [DOMAIN\RptSrvc];
GO
EXEC sp_addrolemember 'db_owner', 'DOMAIN\RptSrvc';
GO

After running this, verify that a simple query against an SSRS catalog view (e.g., SELECT TOP 1 * FROM dbo.Catalog) succeeds under the RptSrvc account.

Restore the Encryption Key on the Destination

Back in Report Server Configuration Manager on the destination server, open Encryption Keys, choose Restore, select the .snk file you exported earlier, and enter the password.

Validate the Web Portal

Finally, open your SSRS Web Portal URL in a browser. Navigate through several folders and render a few reports. If you encounter a 404, authentication failure, or rendering error, inspect the SSRS service log (ReportServerService__*.log) for clues—whether it’s database connectivity, decryption, or URL binding—and correct the underlying issue before retesting.

Conclusion

Export the key first, restore, fix ownership and logins, recreate the service-account user, then restore the key. In that order the surprises mostly go away.

The shared data sources are the part that still annoys me. They’re in the database, the reports reference them, and they don’t come across in a way that works, so every migration ends with recreating them by hand and clicking through reports to check which ones point where. I’ve never worked out whether that’s a real limitation or whether I’m missing a step, and I keep meaning to script the whole thing in dbatools and finding something more interesting to do instead.