Question – How to migrate all the reports and data sources deployed on the old server to the new server?

Scenario – Our Company is going for the new server setup by discarding the old servers and the Reporting Services machine is one of them. This usually happens may be to change or upgrade the underlying hardware or to upgrade software like moving from Windows Server 2003 to 2008, from SQL Server 2005 to 2008 etc. As a DBA, a task is assigned to me to setup the new Reporting Server same as the existing one. This should happen within minimum time and without any loss.

Solution – The choices available before me are,

  1. Deploy all the reports and data sources from BIDS project and manage the report access.
  2. Backup and restore the existing ReportServer database to the new instance.

Here, I will be talking about the second option. I feel that it should be the best one as it is quick and setup the new environment with all the data sources, reports and permissions as is that are available on the old environment.

Let’s suppose that you have got the new machine with SSDS and SSRS installed. The default configuration for SSRS is also done meaning new ReportServer database is configured on the new environment. In that case, follow the below steps to migrate the old ReportServer database.

On the old Reporting Services environment,

  1. Backup the ReportServer database
  2. Start the “Reporting Services Configuration Manager” and open a connection to the report server.
  3. On the “Encryption Keys” page, backup the Encryption Key. You need to specify password at this time.

On the new Reporting Services environment,

  1. Stop Reporting Services service.
  2. Copy the ReportServer database backup taken on the old environment and restore it onto the new environment.
  3. Start Reporting Services service.
  4. Start the “Reporting Services Configuration Manager” and open a connection to the report server.
  5. On the “Encryption Keys” page, restore the Encryption Key which you have backed up on the old environment. You need to specify the same password to restore the key.

Simply restart the SSRS service and now you have all the reports and data sources with user access available onto the new environment. I have tries the same to migrate the reporting environment from SSRS 2005 to SSRS 2008 and it is working fine for me.

Caution – Try this on the test server first before moving directly onto the live and note down the steps  :)



Amit Karkhanis

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook