By: Dale Kelly | Updated: 2012-03-19 | Comments (39) | Related: 1 | 2 | > Reporting Services Migration
Problem
I have some SQL Server 2005 Reporting Servers that have many reports, data sources and subscriptions loaded on them. My company is in the process of moving from SQL 2005 to SQL 2008 and 2008 R2. I need to move all the objects off the 2005 Reporting Server to the 2008 Reporting Server. Is there an easy way to do this without saving each report file (edit report and save .rdl) then uploading the report to the new server? This would also mean recreating the data sources and subscriptions. In our case there are many authors and reports are not managed in any formal way so getting the original reports will be extremely difficult.
Solution
I started looking at this task and at first was a little overwhelmed. I did not know of any easy way to move hundreds of reports, data sources and subscriptions from one server to another. I knew I could save each report as an .rdl then upload it to the new server but that would still leave all the data sources and subscriptions. I thought about the number of days it would take me to move the reports and other objects this way so started to look for alternatives. Most of my internet searches returned results that required moving the report server databases from one computer to another (click here to see this process). This approach requires moving encryption keys, moving databases then fixing any issues that may occur as a result. I thought there must be a better way so I kept looking. Then I found a reference to a tool called Reporting Services Scripter. This tool is a .NET Windows forms application that can be used to script out and transfer all SQL Service Reporting Services catalog items. The tool was created by Jasper Smith and can be downloaded here. The download is a single zip file. There is no installation required, just unzip the file to a folder. The tool requires .NET Framework 1.1 and to generate command files or use the Transfer mode the RS.EXE Reporting Services Management Tools command line utility is required.
To use the tool simply double click the RSSCripter.exe file. If presented with an 'Open File - Security Warning' acknowledge the warning by clicking Run and the tool will open.
Once the tool is open click the Options button to configure the tool for your environment.
Be sure to set the Default Script Directory, this is the location the tool will put the generated script files in. Next click the Servers tab to add your reporting servers to the grid. The first column is just a label and can be anything you like, but it must be unique. The second column is the actual link to the reporting server and should be of the form http://servername/Reportserver/ReportService.asmx for SQL 2000 servers and http://servername/Reportserver/ReportService2005.asmx for SQL 2005 and 2008.
I am not going to go through each tab; the tool includes a very good readme that describes all the available options. There are a few things to note. To include report subscriptions click the Report tab and check the Include Subscriptions check box. The Global tab is used to define the location of the RS.EXE file, the database authentication and the scripting mode. Once all options have been set you are ready to run the tool.
First select a server from your server list by clicking the drop down labeled Report Server. Once a server is selected click the Get Catalog button. This will list all the catalog items on the reporting server.
You can select individual reports by expanding the report folder and selecting the report or select the entire folder. Once you have selected the objects click the Script button and the script files will be generated in the folder selected as the Default Script Directory. The tool will create script files for all objects selected and it will create a command (.cmd) file for loading those objects on a server. In the command file there is a line that sets the URL of the server to script the report objects to, change this line to the server you want to transfer your reports to:
SET REPORTSERVER=http://servername/ReportServer
Also be sure the line for the location to the RS.EXE file is correct.
SET RS="C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RS.EXE"
Once the command file has been edited copy all the files to the new reporting server and execute the command file by double clicking the file in Windows 2003 and right clicking and selecting Run As Administrator in Windows 2008. When I used this tool to migrate to the new server 136 items were migrated and it took just a few minutes. When I inspected the new reporting server and compared it to the old all items I ask for were on the new server, intact and functioned the same as on the old server.
Conclusion
This is a good tool that works very well for what it is designed for. It saved me countless hours completing my task in less than an hour. I did not have to worry about encryption keys, security or stuff left over from a previous version like I would have if I had used the move database method.
Next Steps
- Download the Reporting Services Scripter tool here.
- Unzip the file to a folder.
- As always test on a test server before using in production.
- Double click RSScripter.exe, click the Options button and configure the tool.
- After executing, copy the generated files to the new server edit and execute the .cmd.
- Inspect the new server.
- For more information check these references.
About the author
Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-03-19
