SQL Server Reporting Services: Migrating SSRS reports, data sources and subscriptions from one server to another

As part of our SQL Server infrastructure consolidation project, it was decided to consolidate multiple companies SQL Server 2005 / SQL Server 2008 Reporting Services instances to a new dedicated SQL Server 2012 Report Services instance. These existing SQL Server Reporitng Services instances hosts many business critical SSRS reports, data sources and subscriptions. In order to complete this task, I’ve to make sure that, all reports and other artifacts are migrated successfully from the existing SQL Server 2005/ SQL Server 2008 Reporting Services instances to the new SQL Server 2012 Report Services instance.

Well, we all know that this is not a simple task that can be performed manually, because it involves, manually saving each report file locally on these Report servers manually, and then moving and uploading the report files into the new location of SQL Server 2012 Report Server? This also means that I have to again create data sources and subscriptions for these reports.

Fortunately, after doing some research, I found two tools that can be used for this Reporting Services migration task.  The first tool is Reporting Services Migration Tool from Microsoft, that can be used for migrating reports and other artifacts from SQL Server 2008 R2 and later versions report servers to the new SQL Server 2012 Report Server. This tool uses PowerShell for migration scripts. Here is the link to Download the SQL Server Reporting Migration Tool.  For more information about how to use this tool, see Reporting Services Migration Tool.

Although, Reporting Services Migration Tool is a great tool for migrating SQL Server 2008 R2 or later releases Reporting Services instances. However, it is not a suitable for this consolidation situation because I also have few SQL Server 2005 Reporting Services instances. This is where this second tool that is a freeware tool called ReportSync comes handy. You can use this tool to migrate the reports, data sources and subscriptions from SQL Server 2005 or later releases Reporting Services instances to any SQL Server 2005 or later releases Reporting Services instance. The use of this tool is quite simple, as it only requires you to specify the ReportServer URL of your SSRS server, with the user credentials to log on to the server. The following is the snapshot of this tool:

ReportSync_Main

You can download ReportSync tool from here.

About these ads

19 thoughts on “SQL Server Reporting Services: Migrating SSRS reports, data sources and subscriptions from one server to another

  1. Our problem here is the migration of the REPORTSERVER and REPORTSERVERTEMPDB on a soon to be defunct instance [MS SQL 2005] to a newer MS SQL 2008 R2 instance where there already exists a REPORTSERVER and REPORTSEVERTEMPDB: We just want to migrate those older objects over to the newer instance with minimal disruptions. The existing editing of the reports to point to the NEW instance is a secondary daunting task.

    Other than writing/finding scripts to do this, and then editing each report to point to the new connection instance DB, we are not sure what else to do: Are there any suggestions/tools in this arena?

    Like

      • thanks for your answer, but that was acknowledged in my response. Perhaps my inquiry was too off topic but do you know of a tool or other helpful set of scripts, whatever, that help us make the migration of those two Reportserver dbs over to the new instance or am I just getting this concept wrong?

        Like

  2. I want to migrate reports from SQL Server 2008R2 to SQL Server 2012 using ReportSync (I can’t use Migration Tool because both reporting servers run in native mode). Migration fails because of the following error: Upload failed.System.Web.Services.Protocols.SoapException: The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a late version of Reporting Services or contain content thet is not well-formed or not valid based on Reporting Services schemas. Details: Invalid character in the given encoding. Line 1, position at Microsoft.ReportingServices.Library.reportingService2005Impl.CreateReport … and so on.

    Did you meet similar problems? What can be the cause? There is no problem when I migrate reports manually.

    Like

  3. I Need to migrate reports from 2008 R2 Share point mode to 2012 Native mode.
    Can we use any Script tool for this and let me know steps involved in it.

    Like

  4. Pingback: PL/SQL (Programming Language/Structure Query Language) and SQL reportsSQL Reports

  5. Thanks fr sharing! Cn you please give more details on creating the Subscriptions and Data Sources on destination server? I tried, but it seems not working as expected. Version I used was v1.1.2.

    Thanks!

    Like

  6. Great article. Thanks for sharing migrating tools Basit. My company also has a need to switch from SQL server 2005 to SQL server 2012. Beside apps running on SQL servers, there are about 200 reports running on SQL server 2005. It’s really efficient when I can do the “migrating process” automatically that takes about 2-3 min instead of doing manually one-by-one report (download from SSRS 2005, then upload to SSRS 2012). The tool that I test is ReportSync that is very friendly than Migrating Tool from Microsoft.

    Again thanks so much for your article!
    Great job to me.
    Thanks

    Edward JD

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s