Migrating Your SSRS ReportServer Database

Migrating SSRS ReportServer databases often looks simple on paper: copy a .bak file, run RESTORE, and expect everything to work perfectly. But the reality is different—encryption keys, service accounts, and user mappings can create serious headaches if you don’t handle them properly. I’ve seen migrations fail because the new database couldn’t decrypt connection strings or users got locked out due to SID mismatches.

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, go to Encryption Keys, select Backup, and choose a secure path for the .snk file. Use a strong password and store both 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 confirm 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 to 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 the sa account as the database owner. Run:

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 (usually 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, recreating the SSRS service-account user, and then restoring keys, you avoid the usual surprises. I’ll eventually wrap these steps into a PowerShell module, but for now the process is straightforward enough and really doesn’t take that long.