SQL Server – Migrating SSRS reports from one server to another in one go (folders or full site)

In my last blog post, I talked about an effective and easy to use restore script, which can help DBAs in many scenarios wherein one of scenarios I mentioned was migration. Migration in itself is a vague term as it can be used in many different contexts by individuals for e.g. migrating objects from one server to another of same version or migrating objects between servers of different versions etc.

In this blog post I intend to cover one such aspect i.e. migration of SSRS reports and objects from one server to another. I’ll try to cover up various scenarios that may arise and what approaches are available to do the same.

Problem

There are various scenarios that one can identify while thinking of migration of SSRS objects are like migrating objects from one server to another may be due to better hardware or setting up or refreshing non-PROD report server or migrating to another server of higher version or migrating a part or particular folder from PROD to non-PROD servers etc. The intention to put up this post is to consolidate all this information as what I felt is that clear instructions around all cases is either not available or are too scattered making it difficult to find them.

Solution

Scenario 1 – Upgrade

First up in the list of scenarios is “upgrade or migration of SSRS server”. Thankfully we have most amount of information available for this scenario as with each SQL version release, Microsoft puts a step-by-step guide on how to upgrade (in place) or migrate (side-by-side) your existing SSRS setup to latest version. Below is the URL where in you can access all related information. I’m putting up SQL 2014 and SQL 2016 articles as they are available on different URLs.

For SQL Server 2014

https://technet.microsoft.com/en-us/library/ms143747(v=sql.120).aspx

For SQL Server 2016

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/upgrade-and-migrate-reporting-services

By default the above article includes steps for In-place upgrades and have forward links for side-by-side migration.

Scenario 2 – Movement

There can be cases when one needs to move SSRS objects from one server to another for some reasons like better hardware specs, or creating copy of PROD environment for non-PROD use etc. There are multiple approaches available for this scenario.

1. One can use the migration option as used while upgrading the SQL Server. Again as we have already mentioned Microsoft got us covered here in this case. Links for it are as follows

For SQL Server 2014

https://technet.microsoft.com/en-us/library/ms143724(v=sql.120).aspx

For SQL Server 2016

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode

2. In case you think your existing environment is messed up or don’t or can’t use PROD encryption keys in NON-PROD or something like that, then we have alternative methods as well.

a.       Full Movement (full site)

Below is very interesting RSS script (along with examples) that can be used to move SSRS objects from one server to another without involving DB or encryption key restores on destination server.

https://docs.microsoft.com/en-us/sql/reporting-services/tools/sample-reporting-services-rs-exe-script-to-copy-content-between-report-servers

b.      Partial Movement (folders only)

If you like powershell scripts more than don’t worry. Below link gives you a great powershell script that can download all SSRS objects into a desired folder and then that can be used to move and deploy objects to other server.

https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227

To upload all at once simply add them to new SSRS project using SSDT and deploy to destination server.

The advantage of this method however is that it can be used to do partial movements i.e. in case you want to refresh or move only a particular folder of SSRS from one server to another server. If you think of it there is actually no direct way available to do a partial movement. There are tools available like RSscripter but that has not been updated to support SSRS after 2008 R2 and also has some glitches like re adding data source information whereas in this method there are no such issues and in fact it’s much more helpful and reusable.

First, to get desired objects you simply have to pass folder path in the select query and it will download objects from that folder alone. For example:

# Select-Statement for file name & blob data with filter. 

$sql = "SELECT CT.[Path] 

              ,CT.[Type] 

              ,CONVERT(varbinary(max), CT.[Content]) AS BinaryContent 

        FROM dbo.[Catalog] AS CT 

        WHERE CT.[Type] IN (2, 3, 5)and CT.[Path] = CT.Path = '\PROD\'";

As this powershell script is using a T-SQL based select statement to get objects, we have the flexibility to try and use various combinations to get the desired objects. In case you’re wondering what is denoted by values of “Type” column here then below is some brief for it:

Value 2 refers to Reports

Value 3 refers to Resources (like image file etc.)

Value 4 refers to Linked Reports

Value 5 refers to Data Sources

Add these objects to your new or existing SSRS project and deploy to destination report server to refresh only that particular folder in a quick and easy way. Moreover as we’re adding these objects to a SSRS project we have the flexibility to reuse the same project to deploy or refresh multiple environments without any rework.

So, here it is, I’ve tried to cover general scenarios of migration along with tool, ways or scripts which can help you achieve that. Please 1+ and share if you like the post and also leave your valuable comments.