Migrate reporting services Database to Another Server

Introduction 

Migrate SQL Server Reporting Database from SQL Server 2005 to SQL Server 2008R2 Instance

Using the Below Steps 

SQL: ServerA     to    SQL: ServerB\SQL2008R2 

1. Backup the encryption key & "reportserver "& "reportservertempdb” database in Server ServerA
2. Stop the reporting services in ServerB\SQL2008R2
3. Restore these databases on ServerB\SQL2008R2 on with target reporting database names (ReportServertempdb & ReportServer)
4. Start reporting services on ServerB\SQL2008R2
5. In reporting service configuration tool we will be selecting/upgrading the database.

**** IN SS2008, there is no upgrade option, you need to create a new database or choose from the existing one, so we choose later option (as we have already restored the reportserver & reportservertempdb database) **** 

Click Next  Connection setting (leave it default)

select database screen

Click next & finish …..Your database connection setting (steps 6 also got configured here only)

6. Reset the database connection in the configuration tool
7. Restore the encryption key on ServerB\SQL2008R2

   ---Restore the encryption key from the backup which you have taken in step 1 

8. On the ServerA

Run this command in Query analyzer

'select * from ReportServer.dbo.Keys'

and make note of the InstallationId value for the non-null record

9) On ServerB\SQL2008R2 server,

Run this command in Query analyzer

'select * from ReportServer.dbo.Keys'

and you should see 3 records. One null record, and 2 records that have values in the MachineName field (these should be the old and new server names). The InstallationId value from previous step should be in there with the old server's name 

10. On the ServerB\SQL2008R2 server, delete the record that matches the old server's InstallationId.
11. Verify that the reports are using 2008 engine, steps to be followed for this

Run this command in Query analyzer

USE REPORTSERVER

Select * from ExecutionLog2

Check column called AdditionalInfo that has a <processingengine>element;

If, Process element is 2 --it is using ss2008 DB Engine
If, Process element is 1 -- it is using ss2005 DB Engine

How to configure URL:

Default URL:
https://servername/Reports/Pages/Folder.aspx

Like in ServerA for default instance:
https://ServerA/Reports/Pages/Folder.aspx

Named instance like hillsbor-svr-32\reptdev1
http:// ServerB/Reports_RPTPROD1/Pages/Folder.aspx

restart the reporting services later and check your reporting services.