Microsoft has documented in details how to move Service Manager Data Warehouse databases to another SQL instance. Do not attempt to move the Reporting Services database in the same way because it will fail. I will explain how to move them in another post.
The databases that can be moved between different SQL instances are:
- DWStagingAndConfig
- DWRepository
- DWDataMart
- CMDWDataMart
- OMDWDataMart
The overall steps are listed below. You need SQL admin access and ability to edit SQL tables and registry entries.
- Locate user accounts and instances of SQL Server used by DW databases
- Stop Service Manager services on DW management server
- Back up the DW databases
- Take the DW databases offline (close all query windows)
- Restore the DW databases on the new computer running SQL Server
- Prepare the DW databases on the new database server
- Update DW management Server with the new database server name
- Update the data sources on the existing Reporting Server (Moving RS Database to a different server is not supported – same FQDN server only supported)
- Update the data sources for the existing Analysis Services
- Start Service Manager Services on the DW management server
For details:
https://learn.microsoft.com/en-us/system-center/scsm/move-databases?view=sc-sm-2022
