SQL Server Reporting Services : Migration of reports between Report Manager

Hello Folks !!

This post is another extension of the previous post SQL Server Reporting Service : Multiple reports deletion from Report Manager at One Go, previous post detailed out how to remove multiple reports from the Report Manager using RSS file which subsequently using VB-Script, in this very much post I will explore on how to migrate all the reports or number of reports from one server to another or even on multiple server.

Again, the basic understanding why such requirement ever exists is just because now a days, organization suppose to upgrade their environment to meet the trending pace of Today's world in terms of technology, one such example is when organization decided to move from SQL Server 2012 to say SQL server 2019, in such a scenario, they supposed to move their subsequent reports those are residing in SQL Server 2012 Report Manager to SQL Server 2019 Report Manager, hence need of migration comes into light, again if one has to do it manually, it will be very monotonous, frustrating, time taking , tedious and cumbersome task to perform, hence we again need some advanced or optimised solution that will do the job for developer without wasting so much of developer time which they can utilise in another productive and important work.

Let's explore the possibility of optimised solution for such kind of client request, let's discuss in detail.

Let's start the road roller with some illustration.

Requirement :

Let's say you are working in XYZ organization and as a process of optimisation, organization decided to move their all of their database of existing SQL server to advance or new version of SQL Server, i.e. from SQL Server 2012 to SQL Server 2019, in order to do that, one has to move all of their databases, SQL Server Integration Services (SSIS) ETL packages, Report definition language (RDL) files of SQL Server Reporting service (SSRS) and other required objects to new SQL Server, let's say, client has raised a request for you to migrate all of previous SQL version reports hosted on SQL Server 2012 Report Manager to new SQL Server 2019 Report Manager, once you have this request and post exploration you comes to know that number of report are quite high in number, along with, there are quite a good management of reports and subsequent department reports placed at subsequent folder, plus there are folders for Dataset etc, requirement is to move all of them to new server.

Resolution : 

Again, this request of client is legitimate and billable but very tedious and cumbersome in nature, and literally no one wants to indulge into such kind of activity at least willingly. 

Nonetheless, very straight and simple solution is to move all the reports from source report manager to destination report manager one by one manually, but as a matter of fact, this can be done this way but requires more human efforts and time consumption is quite high, in another words, maximum input and limited output with probability of Human errors, I would never recommended such type of human intervention to deal with one object at a time and high tendency of mistakes and errors, not even in my dreams, so question is how to achieve this tedious goal, be optimistic, we have better solution which is detailed in further in this very post.

Before, jumping into the final solution, it is really  important to understand few concepts which are helpful in undersigning the solution

What is Report Manager ?

Report Manager is nothing but a repository which is available over the internet (or cloud) on which all reports will be hosted, user who having required access can only access the reports.

What is RSS Extension File?

RSS Extension file is what we call it is rich Summary file which is saved on XML kind of format, one can write code on the same and execute it via Command Prompt (CMD).

What is BAT Extension File?

BAT Extension file is script file which is used CMD to perform certain activity.

What is VB Script ?

VB Script is kind of scripting language where we can write a code to perform certain activity, it is same as C# Script with only difference is use of different programming language.

What is Command Prompt (CMD) ?

CMD is default command-line interpreter and also known as CMD.exe

Above provided definition is at very glance, if one want to understand or explore more on these topics, please explore on Google as this topic is out of scope for this post.

Hopefully, all the basic terminology that we are going to use in this solution are self explanatory and easy to understood, lets use these and move towards our goal.

Pre-Requisite 

1 : SQL Sever 

2 : RS.exe utility must be installed

3 : Destination folder must be available (if not available, must create) at destination report manager.

4 : SSRS_Migration.RSS - This is easily available at Microsoft site, one can have it form there.

Source Detail 

Server : ReportServer1

Report Folder : TEST

Destination Detail 

Server : ReportServer2

Report Folder : TEST

To do 

To move all the reports of folder TEST on Report Manager of Production Server ReportServer1 to folder TEST on Report Manager of Development Server ReportServer2

Migration of reports from one Report Manager to another

Step 1 : Open Report Manager of ReportServer2

Step 2 : Check whether folder TEST is available of not, if not, please create folder named TEST. 

Step 3 : Open Notepad

Step 4 : Click on Save As  OR HIT < CTRL + SHIFT + S >  

Step 5 : Under Save As Type , select All Files

Step 6 : Provide File Name as MigrateReport.bat and write below code

D:\>D:\SSRS\MigrateReport.bat
D:\rs.exe -i ssrs_migration.rss -e Mgmt2010 -s "ReportServer1" -v f+"/TEST" -v ts="ReportServer" -v tf="/TEST"

* Below is the snapshot for the same

                                

* Place at same location where ssrs_migration.rss is placed (Optional though, one can place any file at any location, placing at same place require less coding while calling files from CMD 

Step 7 : Open CMD command and execute MigrateReport.bat.

Hurray !!! Here we go, we got the desired result.

Pros and Cons

Pros 

1. This will help you to migrate all the reports from one Report Manager to another. 

Cons 

1. All the shared dataset which uses credentials will not be migrate to another server.

Author - Feel free to ask query if you have any query