Yesterday I was tasked with migrating our Citrix EdgeSight database off of a 2005 SQL Server and migrating it to a new 2008R2 Server.  No problem, I have done that a million times before.  But wait a minute, that database has SSRS reports installed on that server and not our main reporting server.  If you follow my blog, you will probably see that I have not had much experience with SSRS, my reporting experience has been with Crystal and other minor packages.

I started researching how to migrate these reports and was not pleased to see Citrix’s response.  They recommended migrating  the ReportServer and ReportServerTempDB databases to the new server and then reinstalling SSRS on the original server.  So what happens then if you have other jobs on the new and old servers?  This is not a good recommendation.  After an exhaustive search the only option I saw within the tool was to download reports one at a time and upload them into the new server.  This was also not an option with a fair amount of reports to migrate.

Enter Reporting Services Scripter from SQLdbatips.com.  Run the program on the original server and select your Report folder and scripts are generated and placed into a folder of your choosing.  These scripts can then be copied over to the new server and executed by command shell.  In this particular instance we had to reopen the data source and enter new credentials.  Problem solved, reports migrated successfully.

Enjoy!