Hey,
I may be alone on this ship, but migrations from SQL 2005 to SQL 2008 are a happening all over my office, and one of the pains is migrating the non database services surrounding SQL, such as Analysis Services, Reporting Services etc. So after one pain staking migration of Reporting Services, I thought I’d try something else, as even if it exploded and maimed everyone on a school bus, it wouldn’t be any less painful than what I had just done. Lo and behold, it’s probably the simplest thing you could possibly do…
So my assumptions here. You have a SQL 2005 Reporting Services instance, and you have a SQL 2008 reporting services instance. Both are set up and deployed, and you can completely overwrite whatever is on the 2008 Report Server with the contents of the 2005 Report Server.
Step 1
Back up your Encryption Key. Open up Reporting Services Configuration Manager, and go here :
 |
| Back up your key |
Fill in the file location and your desired password, and hit ok.
Step 2
Back up your existing ReportServer database.
 |
| Go on then, what are you waiting for? Back it up! |
Step 3
Copy the key and the database backup to your 2008 server. Restore the ReportServer backup you just took and overwrite the 2008 ReportServer database (The ReportServerTempDB database is fine as it is).
Once the restore is done, open up your Reporting Services Configuration Manager for your SQL 2008 instance and restore the key, like below:
 |
| Almost there…. |
Point it to your key file, and enter your password. Now assuming you have everything else set up and configured as normal, open the web page, and voila! All your reports are there, with their datasources, subscriptions, permissions, medical histories, the lot!
There will of course be times when this won’t be any good to you, but for moving directly from 2005 to 2008 and maintaining the layout of your 2005 Report Server, 60% of the time, it works every time…
Go on, give it a try!
Stephen