SQL Server: SSRS – Moving Reports from Old to a New Environment

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  🙂

 

Regards

Amit Karkhanis

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

   

10 Comments on “SQL Server: SSRS – Moving Reports from Old to a New Environment”

  1. Hi Amit,

    Report migration is done sucessfully.They are working properly but When I subscribe for email configuration but it pointing to old tempserver.how should point to new tempreportserver ?

  2. Check for the SMTP server value in the Email settings option in Reporting Services Configuration Manager.

  3. Hi Amit,

    Prevoius SSRS Email subscribtion is working in inside domain, I want to send email outside domain like gmail. what can setting should done so it will send Email.

  4. Hi, Amit,

    I follow the exact same steps as you suggested from windows 2003 to Windows 2008r2, both machine are using SQL2008R2.

    After migration, i can access to the reports with domain admin user, but my users were getting error, please see the following:
    •An error has occurred during report processing. (rsProcessingAborted) •The execution failed for the shared data set ‘BeLastDate’. (rsDataSetExecutionError) •Cannot create a connection to data source ‘ Data source for shared dataset’. (rsErrorOpeningConnection)

    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Can you please advice?

    Thanks

    Allan

  5. Hi Allan, check the report in question for User Access to ensure that the access rights got migrated properly. If the report is showing Data Source issue, then check for the Shared Data Source for valid username and password (I am asuming here that the report uses a Shared Data Source.

  6. Hi, Amit,

    You are right. The user was not granted with the access right on SQLserver, after the login user created, the user was able to access the report.

    Thanks

    Allan

  7. Hi Amit,

    Thanks for nice info. In my case I need to change database too which old reports pointed.

    Suggest?

    Thanks,

    neeraj

  8. Hi,

    We have 200 subscription in our reporting server, how do i move the to another server at once, its a pain to recreate all of them 1 by 1.

    Thanks in advance

    Abel

  9. Hi Abel, please have a look at “Reporting services Migration Tool” from Microsoft. Also heard about some reportsync tool which is a freeware and seemed to be simple and easy to use. Not yet tried any of them but sure they can help you out.

Leave a Reply

Your email address will not be published.