In the previous post, I explained the steps of moving the Service Manager Data Warehouse databases to another SQL instance. In this post, I will share the details of how to move the Reporting Server database along with Analysis Service.

Unlike Data Warehouse database migration, you cannot migrate Reporting Server databases to any instance. The below requirements exists:

• The server name that hosts reporting server databases cannot be changed.
• The SQL instance that hosts reporting server databases cannot be changed.

That means, if you want to migrate the databases to a new server, you need to make sure the new server and SQL instance names match the old naming.

Below is the steps for successfully migrating the Reporting Server databases.

1. Back up the SQL Reporting Services databases (ReportServer and ReportServerTempDB)
2. Back up the encryption key by using the Reporting Service Configuration
3. Back up Analysis Server database
4. Shutdown the server hosting SQL Reporting Server.
5. Start the new server to host SQL Reporting Server – this server must have the same computer name as the original server.
6. Install SQL in the same instance as the previous server.
7. Install SQL Reporting Server on the newly created SQL instance.
8. Configure SQL Reporting Services for Remote SQL Server Reporting Services.
9. Restore the Analysis server database and update the connections and user password.
10. Restore the Report Server databases. (take a backup first otherwise it will give error; kill connections to the report database – EXEC sp_who2 ; Kill)
11. Restore the encryption key over the newly created SQL Server Reporting Services databases, and verify the report manager URL can be accessed and reports exist