Using RS Scripter to backup/restore SSRS's report/subscription/roles...

f you are using SSRS then chances are high to come across a scenario where you want to deploy reports developed on development machine to the production server, there are various ways to do this and one of them is to use RS Scripter tool. Here are the steps to do this -

1. Creating Report Deployment Script

1. Download the Reporting Services Scripter - http://www.sqldbatips.com/showarticle.asp?ID=62
2. Update the servers.xml; change the reportservice value if your ReportServer name is different.

   
   
   

3. Run the RSScripter.exe.
4. Click on Options, and select the options selected in images below( for more information about Scripting Options check the readme file - http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_scripting_options) –
   
   
   
5. Change the SQL 2005 RS EXE Location in the Global tab as per the location of SQL server and Apply –
   
6. Select SQL 2005 from the Report Server dropdown and click on Get Catalog, all the reports, data source, Schedules, Roles etc present in report server will be displayed –
   
   
7. Select all the reports, data source, schedules and roles you want to transfer.
   
   

Change the output directory path (Should not be machine/user specific e.g. Desktop) e.g. C:\AClickReports and click on Script. The script for loading the specified items will be generated to the specified folder.

2. Deploying the Reports

1. Ensure that you have IIS and dot net framework 2.0 is installed on report server.
2. While installing SQL server 2005 select reporting services option.
3. Follow the report server configuration steps and make sure "Report Server Configuration Manager" is all green ticks?
   
4. Follow these steps to publish reports –
   1. Extract the AClickReports folder to the report server, C: Drive.
   2. Open the “RS Scripter Load All Items.cmd” file located in AClickReports folder for editing.
   3. Change the SCRIPTLOCATION Script variable value to the location where AClickReports folder is copied (Should be same to the output directory path selected in step 3.8)

        SET SCRIPTLOCATION=C:\ AClickReports\
      

      
      
   4. Change the RS Script variable value to the location where Microsoft SQL Server is installed (if it is different from the default C drive).

      SET RS="C:\Program Files\Microsoft SQL Server\90\Tools\Bin\RS.EXE"
      

   5. Run the RS Scripter Load All Items.cmd batch file, it will publish the reports to the report server.
5. Follow these steps to verify that reports are deployed correctly
   
   1. Go to http://localhost/Reports/Pages/Folder.aspx,
      
   2. Open the Data Sources you should see data sources selected in step 1.7
      
   3. Open the data source and update the data source details as per the configuration of report server(if they are different) and apply.
      
   4. Go back to Home page and click on the report folder selected in step 1.7 , select one of the reports and click on Edit button, then click on the Data Sources link ; Make sure that the data source is configured properly.
      
   5. Repeat the steps from 2.5.2 to 2.5.4 for other data sources and report folders.