Migrating SSRS 2008 R2 SharePoint mode from one SharePoint Farm to another

In general, master copies of SSRS Content types items like Reports (.rdl), data sources (.rsds), semantic models (.smdl) etc. are stored in the SharePoint content DB and cached on-demand by SSRS in the RS catalog. Additionally, report / data source metadata like subscriptions, schedules, snapshots, stored credentials etc. are exclusively stored in the RS catalog. Having said that, all these reporting items stored or cached within the RS catalog are associated / linked with where they are stored in SharePoint. These associations generally comprise the SharePoint site GUID, relative paths wrt to the SharePoint root site, etc. Hence, in general a large portion of data stored in the Report Server catalog DB in SharePoint mode is relative to or dependent on SharePoint content/ sites. As a result, simply migrating the backed up reporting services database into another SharePoint farm is not going to work in every case.

SSRS migration (with backup and restore) needs to be step-by-step with SharePoint configuration / content DB migration. Hence the best practice / supported means:

  • When the entire SharePoint farm with its content DBs as well is migrated to a new SharePoint Farm then you should consider migrating the SSRS catalog DBs as well via steps identified at SSRS migration (with backup and restore).
  • When only SSRS DBs are being migrated from one SharePoint farm to a completely different farm without also migrating all the SharePoint content DBs, then RS catalog DB migration is not the recommended nor supported means. The right means in this case is migrate the SSRS data using tools like rs.exe utility in SSRS 2008 R2.