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.

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.

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

By exporting and restoring the encryption key first, verifying each database-restore step, fixing ownership and logins, re-creating the SSRS service-account user, and then restoring keys, you eliminate the usual surprises. I’ll eventually wrap these steps into a PowerShell module, but for now the process is straightforward enough that I’m focusing on more complex challenges.