Migrating SQL Reporting Services to a new server by moving the Reporting Services databases

By:   |   Comments (38)   |   Related: 1 | 2 | > Reporting Services Migration


Problem

I recently had a task to move SQL Reporting Services to a new server. While researching I found two methods to accomplish this task. One used a scripting tool which I wrote an article about and can be found here. The other method is the one recommended by Microsoft. This method physically moves the ReportServer database to the new server. So how do we move Reporting Services by moving the database?

Solution

If you do a search for how to move a SQL Reporting service to a new server you will find a Microsoft document detailing the steps required. Click this link to view the document. My initial reaction after reading through this document was there were a lot of steps and moving parts and that is why I initially used the scripting tool. But to be fair, I went through the process detailed in the Microsoft document and wanted to present it here.

My environment is simple, a single SQL 2005 server moving to a single SQL 2008 R2 server with no Scale-Out deployment, no custom assemblies and no custom configuration settings. So here we go.

Before starting the migration be sure to be aware of the changes listed in the document that may affect how you migrate your installation. In my case I did not have to worry about any of these changes so proceeded directly to the Backup Files and Data section of the document.

Backup Files and Data

Backup the encryption key for the report server database. The report server encryption key is backed up using the Reporting Services Configuration Manager. For SQL 2005 navigate to Start/All Programs/Microsoft SQL Server 2005/Configuration Tools/ and click Reporting Services Configuration. On the Instance Selection dialog enter Machine Name and Instance Name and click Connect.

Reporting Services Configuration Manager

In the navigation panel on the left select Encryption Keys, then click Backup. Enter a Password in the Encryption Key Information dialog and click the button next to Key File to specify a location and name for the key backup file.

Reporting Services Configuration Manager

Click Exit to exit the Reporting Services Configuration Manager. Microsoft recommends backing up the report server configuration files. As I went through the process I did not need these files later in the process, but backed them up anyway. Here is a list of the files and their locations in a SQL 2008 installation on the E: drive.

  • Rsreportserver.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • Rssvrpolicy.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • Report Server web.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • Rswebapplication.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • RsmgrPolicy.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • Report Manger web.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • ReportingServicesservice.exe.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
  • Machine.config for ASP.NET if you modified it

Backup the report server database using any supported method for backing up SQL Server databases. I choose to backup the databases using SQL Management Studio. Microsoft states that both the ReportServer and ReportServerTempDB databases should be backed up and restored to the new server. In SQL Management Studio expand Databases and right click the ReportServer database, select Tasks and select Back Up....

SQL Management Studio

Make sure the Backup Type is set to Full and specify a location and filename for the backup.

SQL Management Studio

Do the same tasks for the ReportServerTempDB database.

Note: If the database is using the Full recovery model, use the following script to back it up to avoid breaking the log chain, 'With Copy_only' will make a copy only backup and will not break the log chain:

Backup Database ReportServer To Disk='E:\Temp\ReportServer_04192012.bak' With Copy_only

Move Report Server Database and Configure Report Server

If you are moving the databases to a server that has never hosted SQL Reporting services you will need to create the RSExecRole, click this link for instructions.

When ready, restore the ReportServer and ReportServerTempDB databases to the new server. In my case I am replacing the existing Report server databases. In SQL Management Studio expand databases and right click the ReportServer database, from the pop up menu select Tasks/Restore/Database.

SQL Management Studio

Make the entries in the Restore Database dialog appropriate for the backup you took. In my case it is a file stored on the local disk. Note: To gain exclusive access to the database for the restore I had to stop the SQL Server Reporting Services (MSSQLSERVER) service.

SQL Management Studio

In the Options page of the Restore Database dialog check 'Overwrite the existing database (WITH REPLACE)', leave the database ready for use and use the buttons under 'Restore As' to locate the physical database files (existing .mdf and .ldf files).

SQL Management Studio

Once the ReportServer database has been restored repeat the process for the ReportServerTempDB database. If your reporting installation includes custom items you must re-deploy those items, refer to the 'Move Custom Assemblies or Extensions' section of the migration document. My installation does not include custom items so I will not cover this section. Now that the databases and custom items (if any) have been moved you must use the Report Services Configuration tool to configure Reporting Services. Navigate to Start/All Programs/Microsoft SQL Server 2008 R2/Configuration Tools and click Reporting Services Configuration Manager. Enter the Server Name and Report Server Instance in the Reporting Services Configuration Connection dialog and click Connect.

Reporting Services Configuration Manager

If you stopped the Reporting Services service during the restore process click the Start button to start the service. If needed configure the URLs for the Report Server and Report Manager, instructions can be found in the Migration document under the Configure the Report Server section, I did not need to do this. For Reporting Services to recognize there is a different database you must go through the Configure Database process. In the Navigation panel click Database then click the Change Database button.

Reporting Services Configuration Manager

The following images show the screens and settings when configuring the database.

Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager
Reporting Services Configuration Manager

Now restore the Encryption Key from the database you restored. In the Navigation panel of the Reporting Services Configuration Manager select Encryption Keys and click the Restore Button. In the Restore Encryption Key dialog click the ... button to locate the file you created when backing up the Encryption Key from the server you are migrating, enter the password you used and click the OK button.

Reporting Services Configuration Manager

Now this is where I had trouble. According to the Migration document, once the Encryption Key has been restored things should be working. When I tried to access my Report Server I got an error stating 'The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported)'. I opened Reporting Services configuration Manager and clicked Scale-out Deployment in the navigation panel and saw that there were two servers listed, the old one and the new one.

Reporting Services Configuration Manager

I did not find any mention of this in the migration document. After searching the internet I found some articles describing this issue and the fix. There are a couple of ways to fix this but the simplest is to delete the record corresponding to the old server in the Keys table of the ReportServer database. Once I deleted the record for the old server Reporting Services started working as expected.

Conclusion

After going through this process I found that yes, it does migrate my reporting server, but at the same time I felt there were a lot of moving parts and steps involved. There was also an undocumented issue that I had to address. When comparing this to the RSScripting tool this method was more difficult and provided much less control. With this method I have no control over what was migrated, everything gets migrated. With the RSScripting tool I can control what gets migrated and I did not experience any issues.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dale Kelly Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Sunday, May 17, 2020 - 11:44:11 PM - Ashwani Back To Top (85684)

Thanks for the post, really helpful. One thing I want to mention is that after this process, data source connection string did not show up on new SSRS instance and I could fix it as steps mentioned in below blog:

https://stackoverflow.com/questions/30080995/losing-datasource-credentials-when-restoring-reports-server-database-from-backup

Friday, November 16, 2018 - 11:06:16 AM - Reginaldo Silva Back To Top (78274)

Thanks a lot, great post, saved a little hours of my life in actual days.

Regards.

Wednesday, October 24, 2018 - 3:57:00 PM - Jeremy Back To Top (78046)

 

 I followed the instruction but my subscription jobs are still not available on my new server 

Please, any help?

Thanks

 

Friday, September 21, 2018 - 12:01:26 PM - Wycliffe Back To Top (77688)

The above instructions are on point. worked for me perfectly. the only thing one has to note is that the SCALE  OUT option is not avilable on SQL Server 2016 Reporting Services Configuration Manager. What you need to do to remove the older server name is delete the entry from dbo.keys table after doing a restore of your encryption keys. 

DELETE FROM [ReportServer].[dbo].[Keys]

WHERE MachineName = 'NJROS1BBLD0504'

Friday, February 2, 2018 - 12:57:04 PM - Tito Back To Top (75089)

If after migrating, your SSRS LogFile fills up your logfile destination folder, run SQL Profiler and see for the following

exec msdb.dbo.sp_delete_job @job_name=N'1EE79B76-2C86-4579-83E3-18F713C669BD' or some other job name. If you see it running every few seconds check to see if that job really exists. If not create it (dummy job) then the delete works and voila . 0 log files!

Wednesday, January 24, 2018 - 3:19:07 PM - Ramil Back To Top (75033)

Thank you for the tip. Followed your instructions and my new report server is now up and running. Thanks again.

 

Thursday, August 17, 2017 - 4:38:59 PM - Dev Back To Top (64978)

Very good article.

Could you please let me know if  SQL Server keeps the SSRS Encryption Key backup location anywhere?

Is there any table/registry key which keeps this information?

 

Thanks in advance.

Dev

 

Friday, July 14, 2017 - 4:20:15 PM - Alin Mihalcea Back To Top (59362)

 U da man! Did exactly as you seaid and it worked! Thank YOU!!!!!!!!!!!

 

Thursday, May 25, 2017 - 2:44:12 AM - Kal Back To Top (56027)

Thanks for the article it helped me with my migration from SSRS 2008R2 to 2012

I ran into the same problem you did when restoring the Encryption key and i encountered the error below when trying to delete it

Microsoft.ReportingServices.WmiProvider.WMIProviderException: No report servers were found. —> System.Management.ManagementException: Invalid namespace
   at System.Management.ManagementException.ThrowWithExtendedInfo(ManagementStatus errorCode)
   at System.Management.ManagementScope.InitializeGuts(Object o)
   at System.Management.ManagementScope.Initialize()
   at System.Management.ManagementScope.Connect()
   at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String machineName)
   — End of inner exception stack trace —
   at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String machineName)
   at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstance(String machineName, String instanceName)
   at ReportServicesConfigUI.Panels.ClusterManagementPanel.ConfigureWebFarm(Object sender, RSReportServerInfo[] rsInfos)

 

The solution i followed for anyone who also runs into the same issue is found under the following link:

https://chayadigital.wordpress.com/2014/04/02/how-to-migrate-reporting-services-from-sql-server-2005-to-sql-server-2012/

Thanks

Kal

Friday, March 31, 2017 - 11:36:55 AM - Michael Back To Top (54035)

Genius!  Thanks for these notes, saved me a ton of time. 

 

Wednesday, March 1, 2017 - 10:29:02 AM - saji Back To Top (46840)

 excellent, I have "scale out" and edition error crop up, both resolved with deleting old server information from keys table.

Saved my lot of time :)

Thanks

 

Friday, February 10, 2017 - 4:16:32 PM - vanrani Back To Top (46177)

 Hello Dale,

Thank you for your tips, it is very helpful. I had to move only the SSRS db from one server A to Server B. After moving the ssrs dbs, what i suppose to do all the subscriptions jobs created on ServerA SQL agent?

 

Thank you in Advance.

 

Wednesday, May 4, 2016 - 6:11:55 PM - Jim Back To Top (41404)

Thanks for your post, it had the key thing that Microsoft seem not to mention

But a really important thing to note is that you need to delete the Keys value from the database *before* you go any further. So my steps outline was:
1 Turn OFF the RS service
2 Restore the databases
3 delete from keys where MachineName = 'sourceserver'  -- i.e., do this before the RS service finds it
4 Turn ON the RS service
5 Connect the database into it
6 Restore the encryption keys
7 Open Report Manager

Cheers

Tuesday, April 5, 2016 - 5:39:15 PM - Russell Tye Back To Top (41143)

 

 I thought this was a great presentation. Kudoes to you!

Thursday, August 27, 2015 - 12:57:48 AM - Ganesh Back To Top (38548)

Hi Dale,

Can you give brief about moving ssrs 2008 32 bit to 2012 64 bit.

 

 

 

Tuesday, July 21, 2015 - 3:27:39 PM - Bobby Back To Top (38270)

Just would like to add, I followed every thing mentioned to the T but after deleting the key from the dbo.keys table ( the last step) could'nt access my report manager URL with the error message.

 

THE REPORT SERVER INSTALLATION IS NOT INITIALIZED . Solution is to delete the keys form the reports configuration manager and run the database configuration form the reports configuration manager again.

 

 

 

 

 

 

 

 

Tuesday, March 31, 2015 - 8:43:43 AM - Forrards Back To Top (36775)

If you`re itnerested in Crystal reports to SSRS migration and pros and cons of such proejct take a look here 

http://www.forrards.com/MigrateFromCrystalReportsToSSRS

http://www.forrards.com/ReportingServices

Friday, January 30, 2015 - 10:29:19 AM - CLi Back To Top (36116)

Hi Dale,

Thanks Dale. I have a problem to migrate the ReportServer database from SQL Server 2005 to SQL Server 2012. I have followed your comments. And I am able to cnfigured the restored ReportServer in Reporting Services Configuration Manager 2012 without error. But I am not able to open the link http://localhost:8080/reports or http://localhost:8080/reportserver. Both link are given error "

  • An internal error occurred on the report server. See the error log for more details. (rsInternalError)"
Do you have any advice? thanks here in advance.

 

Thursday, September 25, 2014 - 2:13:38 PM - Singh Back To Top (34721)

Thanks Dale! The Scale-out issue took me some time to figure out, your tip was very helpful. 

Friday, July 4, 2014 - 1:27:44 PM - Manjunath Back To Top (32551)

Hi

I need to migrate custom reports. I saw this text above "Move Custom Assemblies or Extensions" but could not locate in the site. Can some one help me with this document please?

 

Thanks

 

Thursday, April 3, 2014 - 5:21:00 PM - Robin Back To Top (29970)

Thank you so much for the article!

I just finished migrating the report server from SQL Server 2008 to SQL Server 2012 after following the database backup/restore steps mentioned here.

I just had to change a few data sources pointing to the new server , for those reports where the databases has been moved to the new server as well.

Everything works great except that the new reporting services server (http://reportservername/reports)   is  so slow after the migration. Even accessing the folders within takes 30-45 sec average. Do you have a workaround for this? Any help is greatly appreciated. Thanks!

Wednesday, April 2, 2014 - 3:26:31 PM - Craig Back To Top (29948)

For another option, the following topic describes in detail the approach and a sample to copy SSRS items from one server to another using script.  The script supports either SSRS server type.  For example, Native to SharePoint, SharePoint to Native, SharePoint to SharePoint....

"Sample Reporting Services rs.exe Script to Migrate Content between Report Servers"

http://msdn.microsoft.com/en-us/library/dn531017.aspx

Tuesday, July 30, 2013 - 12:13:04 PM - George Back To Top (26060)

Smooth as silk Dale, thanks for the article.

 

 

Tuesday, July 2, 2013 - 10:07:54 PM - Dan Back To Top (25683)

Thanks, the screen shots were very useful. I found that with SQL Server Enterprise, this is the way to go.

I was able to do the migration and simplify the installation at the same time. I didn't need to do the last step. I tried it to see what would hapen and it broke my configuration. I actually need the Reporing Service database to be referenced on a common domain server by two web-facing (DMZ) Webapp servers. I did this using a pair of SQL Server account users.

Monday, June 24, 2013 - 5:49:12 AM - hemanth Back To Top (25534)

Thanks a Lot.

Friday, April 19, 2013 - 12:21:31 PM - Konstantin Back To Top (23445)

Hi Can anyone tell me how to move just one report from one report server to another to perform there a performance test.All the tutorials around the network are how to move everything.

Friday, February 8, 2013 - 1:33:05 PM - Dinesh Back To Top (22017)
 
Nice...

Monday, December 17, 2012 - 7:32:49 AM - Jack Owens Back To Top (20981)

In this URL in 3rd image from top I was able to see your server name in "Object Explorer Details" section as  "CPSHOULD815", might want to edit the image

 

Also based on your TEST Server naming convention anybody woud guess Production Server Name too ;)

Wednesday, August 29, 2012 - 9:08:37 AM - Simon Doubt Back To Top (19274)

Thank you kindly for the post. I, too, found the part about resolving the 'scale out deployments' very helpful. Nothing better than a quick and easy fix!

 

Wednesday, August 22, 2012 - 9:30:44 AM - Nasir Back To Top (19168)

Well done... 

 

Many thanks for your post..

Monday, May 21, 2012 - 8:29:15 AM - Dale Back To Top (17568)

James;

If you are refering to the subscriptions on the old server, yes you should disable these. It can be done by going to the reporting server and removing or disabling the subscription on each report. Or, you can disable the job under SQL Agent. You will need to determine which job is for what report since they do not have nice titles. I was able to find a script on the internet that will list the report and the associated job. Here is a link http://www.mssqltips.com/sqlservertip/1846/how-to-easily-identify-a-scheduled-sql-server-reporting-services-report/.

Sunday, May 20, 2012 - 8:09:24 PM - James Back To Top (17560)

Just a question. Do I need to disable the schedules on the old server? If so how? Thanks.

Sunday, May 20, 2012 - 8:08:26 PM - James Back To Top (17559)

 

Very helpful post especially the part about Scale-out Deployment. Thanks.

Friday, May 18, 2012 - 3:21:27 PM - Dale Back To Top (17539)

Mathew;

When I did this test migration it recreated the subscription jobs once I completed the last step. It did this after fixing the Scale out deployment issue.

Friday, May 18, 2012 - 3:19:15 PM - Dale Back To Top (17538)

Hello Ankit;

I am sorry I have not done a migration from Crystal to SSRS so cannot help.

Friday, May 18, 2012 - 1:02:52 PM - George Su Back To Top (17535)

For Crystal Reports to SSRS convertion, Crystal Migration Services provides the best solution at the lowest price. There is sample conversion available. So take a try and there is nothing loss.

The URL is as follows.

http://www.crystalmigrater.com/Default.aspx

 

Friday, May 18, 2012 - 11:52:47 AM - Mathew Newton Back To Top (17533)

Hi,

What about scheduled subscriptions? These are stored as jobs in SQL Server Agent.

Friday, May 18, 2012 - 9:42:50 AM - Ankit Shah Back To Top (17529)

Hi Dale ,

   It's Nice and very helpful post. i would like to know that have you done conversion or crystal report to SSRS ? I am working on that project now and i need step by step help .so if you or anyone can help me would be much appereciated .

Thanks














get free sql tips
agree to terms