Migrating Reporting Services from SQL 2005 to 2008

13 Jul

I’m right in the middle of quite a large migration from one of our 2003 boxes to a fresh 2008 box. The box in question is the main SQL backend and is currently running SQL 2005, the new 2008 box is running SQL Server 2008.

The migration has been split into phases and I’m currently moving all the front end stuff over, this included reporting services. As it turns out, moving Reporting Services and all the reports from 2005 to 2008 is not that much of a job, mainly thanks to Reporting Services Scripter.

I ran it on the 2k3 box and scripted everything I wanted, this then created a lot of data in the temp folder that I specified, which I then copied over to the new box. I had chosen to Script only, but you can Script and transfer, but I didn’t have the firewall rules in place for this.

For reference, see the ReadMe here.

On the 2k8 box I ran the ‘RS Scripter Load All Items.cmd’ file, but initially had a few problems. I then edited this file, turned the echo on (so I could see what was happening) and examined the log file. In my case it turned out to be that the path to the rs.exe file was incorrect so I had to change this.

@echo on
:: ** Script generated by Reporting Services Scripter **
:: ** Created by Jasper Smith (jas@sqldbatips.com)             **
:: ** See http://www.sqldbatips.com for help/support           **

::Script Variables
SET LOGFILE=”RS Scripter Load Log.txt”
SET REPORTSERVER=http://localhost/ReportServer
SET RS=”C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\rs.exe”

This time it ran for longer and when I checked in Reporting Services all the reports were there. However, the data sources were not and even having manually re-created all the data sources, all the reports were claiming to use data sources that did not exist. I figured if I re-ran the cmd file, it would then this time see that the data sources were there and the reports would all automagically work (and hopefully not make a right mess by duplicating everything). As it turns out, everything worked perfectly, which was nice.