Question:
Are there any tricks to migrating reports, particularly custom reports from a legacy server to a new SSRS server
Answer:
There are several options. The best approach depends on if you can upgrade the original SSRS ReportServer database or not. If not, are the old and new servers both up and running so that a script can connect to both simultaneously.
Best Procedure
- On the old SSRS server, export the encryption key
- On the old SQL server, Backup the ReportServer and ReportServerTemp databases
- On the new SQL server, restore the ReportServer and ReportServerTemp databases from the old server and then use the SSRS Configuration tool to connect the new SSRS server to the restored databases. The tool upgrades everything. Ref https://docs.microsoft.com/en-us/sql/reporting-services/report-server/moving-the-report-server-databases-to-another-computer-ssrs-native-mode?view=sql-server-2017
- Restore the encryption key (done with the SSRS configuration tool)
- Important: On the new SQL server using SSMS, find the “Keys” table. It likely has 2 rows, remove the one with the old SQL server name.
- Run SSRS Report Manager – and wow, all your stuff is there!
Here is some non-microsoft documentation that says pretty much the same thing: https://stackoverflow.com/questions/46876442/ssrs-migration-from-2008-to-2016/46879292#46879292
Microsoft Script
When both servers are available, we have had good luck with https://msdn.microsoft.com/en-us/library/dn531017(v=sql.110).aspx
You do need to edit the DataSource objects (usually in the _Library and _Custom folders in a standard deployment) in order to re-specify the SpitfireRO password.
Remember, if you delete the sfDocSys data source from _Library, ICTool will re-create it for you, but then all reports must be bound to the new data source manually. If you delete the sfDocSys data source, consider deleting all reports from _LIBRARY and all links to those reports!
Additional Comments:
Don’t forget to check your subscriptions!
KBA-01xyz; Last updated: January 6, 2020 at 14:20 pm;