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 can end up with a database that won’t decrypt connection strings or throws permission errors in the portal.

Microsoft’s migration guide for Reporting Services calls backing up the encryption key “critical to migration success.” The report server database holds the reports, shared data sources, subscriptions and schedules, but the stored connection strings and credentials in it are encrypted. Restoring the key on the new server is what makes them usable again.

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. Give it a strong password and store both the file and the password somewhere safe.

Back Up the ReportServer Database

With the encryption key saved, take 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. Microsoft’s guide says the report server database and the temporary database “are interdependent and must be moved together,” and its backup and restore page explains why a backup of it is worth having. You don’t need the data in it, but you do need its table structure, and if it’s lost, “the only way to get it back is to recreate the report server database.” So back up both.

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

Right after, check sys.databases or the SQL Server error log to make sure there were no lock or permission errors during the restore.

Reassign Database Owner and Fix Logins

SSRS requires sa 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';

Microsoft’s documentation for sp_change_users_login marks it as a feature that “will be removed in a future version of SQL Server” and points to ALTER USER instead, so ALTER USER [YourUser] WITH LOGIN = [YourUser] is the longer-lasting way to do the same remap. Either way, check that each database user maps to its server login afterwards.

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, check that a simple query against an SSRS catalog table (for example 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. Microsoft’s guide says this step is “necessary for enabling reversible encryption on preexisting connection strings and credentials that are already in the report server database.”

Validate the Web Portal

Finally, open your SSRS web portal in a browser, go through several folders, and render a few reports. If you get a 404, an authentication failure, or a rendering error, check the SSRS service log (ReportServerService__*.log) for clues, whether it’s database connectivity, decryption, or URL binding, and fix the underlying issue before testing again.

Conclusion

Export and restore the encryption key, back up and move both databases, fix ownership and logins, recreate the service-account user, and then check the data sources. In that order, most of the usual surprises go away. I’ll eventually wrap these steps into a PowerShell module, but for now the process is straightforward enough and doesn’t take that long.