Moving an SSRS database to a new server

I recently needed to migrate a single server SQL 2005 B.I. solution from one box to another. One problem was that the report folder structure, permisisons and so on had been customised quite a lot so the best choice was to simply move the database across to the new server.

This can be achieved as follows:

1. Backup the ReportServer database on the source server
2. Backup your encryption key on existing installation with the Reporting Services Configuration Manager tool found in the SQL Server 2005 | Configuration Tools folder under Windows Start Menu. Place it somewhere you can easily locate it later.
3. Install reporting services on a new server and restore the ReportServer database. You don’t need to restore the tempdb.
4. Run the Reporting Services Configuration Manager tool on this new server.
5. Solve any issues you see, configure to run under a domain account with a strong password (as per best practice), and then on Encryption Keys step, restore the key you saved in above step.

Once i had completed this i had a problem, because the target reportserver had already been configured and initialised. The specific error was: Scale-out deployment is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Some research led me to learn about issues with multiple encryption keys in Standard Edition.. and Chuck’s post pointed me to the RsKeyMgmt command line util to fix up my problem.

A simple fix which resulted in my ReportServer database being moved across successfully.


2 Responses to Moving an SSRS database to a new server

  1. abu sihaab says:

    What if I have to create a new Reporting Database and leaving the default report database alone ? Do I have to migrate TempDB as well? As far as I know, reports will look for the same database name so that I have to have the same DB name in the newer server.

    Any suggestions?

    • bhavikmerchant says:

      If you need to create a new SSRS database on the same server you can always give it a different name – in the SSRS configuration wizard this is done under the “Database” section. When you hit the “Change Database” button you will need to specify a SQL server connection. After this you can type in a different database name, and the old database will still be intact. You never need to migrate ReportServerTempDB, and you cant change its name either. The wizard it will add just append “Temp” (SQL2012) or “TempDB” (previous versions) to whatever your SSRS database name is.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: