We have an existing SQL Server Reporting Services installation running on a pre-R2 installation of SQL Server (10.0.4000.0). Both the application tier and data tier are on the same machine.
I need to move this installation over to another server that is currently running SQL Server 2008 R2 RTM (version 10.50.1600.1). Again, the application tier and data tier will both live on the same machine.
I already have SSRS setup on the new machine and now need to know if I can just backup/restore the ReportServer and ReportServerTempDB databases from the old machine (pre-R2) and have the new installation pick them up and use them. I know that it’s a bit more involved than that (i.e. there’s the encryption key export/import) but I’m mostly concerned about whether or not there are any breaking schema changes that were introduced in R2. Doing a quick look at the empty databases on the R2 server there do appear to be some differences when compared to the databases on the old server.
Am I stuck just manually migrating my reports over to the new server?
Answer
I went ahead and tried this out, and SSRS actually appears to update the schema on the fly. I stopped the service, restored the pre-R2 ReportServer and ReportServer-TempDB, restarted the service, imported the encryption key from the other server, and the did another schema comparison. All of the schema differences had been applied automatically.
In short, this seems like it works fine.
Attribution
Source : Link , Question Author : Jesse , Answer Author : Jesse
