Click here to Skip to main content
12,505,591 members (53,133 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

9.3K views
2 bookmarked
Posted

Migrate reporting services Database to Another Server

, 26 Aug 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Server Reporting database migration from SQL Server 2005 to SQL Server 2008 R2

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)

  1. Reset the database connection in the configuration tool
  2. Restore the encryption key on ServerB\SQL2008R2

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

  3. 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 

  1. On the ServerB\SQL2008R2 server, delete the record that matches the old server's InstallationId.
  2. 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. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Atul_Kapoor
United States United States
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160927.1 | Last Updated 27 Aug 2013
Article Copyright 2013 by Atul_Kapoor
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid