Last week as part of job I had to migrate the Databases and Reporting Service of SQL server 2005 To SQL server 2014 . many of You Already know how to the migration of databases from sql server 2005 to sql server 2014. For better idea read my pervious post here .

So instead of repeating the whole story  again I just thought to share the migration Of reporting Service/server.

Pre-requisite for this is, We need SQL server 2014 Installed with Reporting Services configured  on server where we will be migrating from old server.

We will consider here, source server as SERVER A and Destination server as SERVER B. Reporting Service is installed on both the server and already configured. Reports are also configured and published on Server A as we need to migrate those to sever B .

First phase will beBack up at Source level  SERVER A

Second phase will be  restore at destination level  SERVER B

Third phase will be reconfiguration of reports at SERVER B

So will start now

I will Use SSMS to Back Up The Database. We Need To Back UP The 2 Databases. “Report Server” and “Report ServertempDB”

Right Click on Report Server . Select Task Select Backup. Mentioned the path for backup and the backup file name for full back up.

Repeat the same process for the  Report ServerTempDB .Right Click on Report ServerTempDB . Select Task Select Backup. Mentioned the path for backup and the backup file name for full back up.

As we have the Database Back up other thing we need is The Encryption key from the report server.

So will open “Reporting Service Configuration manager” Connect to the instance where its configured. And select the encryption key tab.

There we will have the option For “Backup”, “Restore”, “Change” and “Delete” For the Encryption Key.

Select Back up

Popup will appear. Enter The Password For the KeyFile And the name and location In the pop block.

Copy These 2 database backups and the Key File at SERVER B and we are done with our work at SERVER A

At second Phase.

We have to reverse this process. Restore the Databases backups on SERVER B.

As reporting service is already configure here we will need to overwrite the existing databases. Click on Report Server <Task< Restore < database .Provide the backup file (Backup From Server A).

Repeat it for Report ServerTempDB. Click on Report ServerTempDB <Task< Restore < database .Provide the backup file (Backup From Server A).

After Databases restores we will need to restore the key file. So open the reporting services configuration manager .

And select the Encryption keys Tab. Same four options will be there as they were on SERVER A. this time select Restore option.

Popup will come. Provide the details for Key file. Use same Password which was used while backing up.

At this stage we need to do some changes in report server. Use below query in SSMS on Server B.

“SELECT *  FROM [ReportServer].[dbo].[Keys]”

U will find the 2 entries there, One with SERVER A and one with SERVER B.

Delete the Entry for Server A. Second stage is over here

Now will  See the third stage.

If we open The “Reporting Service Configuration Manager”. There are multiple tabs . select Report Manager URL tab.

(I assume that Reporting service is already configured).

There You will find the URL . This Is the Url for management of Reports. Basically the url is nothing but the Hostname:port/reports.

Click on this to get new IE tab open or Open the IE and type the Url

You will see the home page of Reporting manger.

As  WE  have migrated it to SERVER B From The SERVER A . It will be exactly look like SERVER A’s Home page.

Select The Data source icon as we will be required to make changes there. After clicking this we will get to see all the data sources. In My case I have only one which is shared data source.

Click On Shared Data source to open Its properties.

Here all this details will be of the SERVER A. We need to change this.

Things needed to change Are

1.Connection string

2.Credentials.

Connection string Box . you will find SERVER A’s Hostname and Database name.

Replace It with The details From Server B.

Now same thing with Credentials box. Replace them with set which will be Use for connecting to SERVER B

Now Test Connection. If everything is right it will be successful.

Apply the changes. And you are done with successful migration Of Reporting services.

Now access The report service URL and you will see all the Reports. You are not required to migrate any rdl files and depoly to server B . just follow this and You all reports will be successfully migrated to new server

Thanks And Enjoy