There are two Microsoft KnowledgeBase articles that will help you move an existing SQL server and Reporting Server for MSCRM to another SQL server in the same domain.
The first is KB917948
The second is KB842425
You will also need the information from this page on how to back up encryption keys from the old reporting server. This file is linked to from the second KB article page.
I recommend printing a hard copy of these documents,
A hard copy of all these documents is available in the safe in the server room. The documents contain a much more detailed, step-by-step list of instructions, but are somewhat incomplete.
First, a complete backup must be made of all relevant databases.
On the old SQL server, back up the following databases:
ART_MSCRM
ART_Metabase
ReportServer
ReportServerTempDB
Copy them to the new SQL server.
Restore the database backups to databases with the same name on the new SQL server. Make sure you choose ‘Overwrite existing database’ under the restore options.
On the CRM Server (not the SQL server), open the Deployment Manager. Click on Server Manager, right-click the CRM server and choose Configure SQL Server. Point it to the new server.
At this point, if you shut down the old SQL server, you should be able to access information in the new SQL server (make sure you run iisreset on the CRM server). Reports will not be working, though.
Follow the step-by-step instructions from KB842425 under the SQL Server 2005 Reporting Services section. There are 11 steps to follow for the destination (new) server. This part covers most of the migration of the Reporting Services. Follow the steps under Other versions of SQL Server 2005 environment for fixing the encryption key problem that comes from restoring keys from the old Reporting server.
I found it a bit confusing, but the SourceEncryptionKeyID they talk about is the long GUID that looks something like this: rskeymgmt -r 31d9a210-3314-4a47-9a09-707693d25a1f
This is just an example. Delete the one that belongs to the old server, and not the new one.
This completes most of the work you need to do to move a CRM SQL database to a new machine.
Here’s what it doesn’t cover:
Check the CRM Server’s \Inetpub\wwwroot\_Resources directory. The isv.config.xml file needs to be edited if you have any custom buttons that point to the old reporting server. We have three.
Next, navigate to \Inetpub\wwwroot\Chief\database.asp and modify it to use the new server’s login and password. This will fix all of our custom pages.
On the old SQL server, there are several PHP files in the \php directory. These also need to be gone over and modified as needed. Copy them to the new server (you could copy the entire PHP directory). Set up Scheduled Tasks on the new SQL server the same as they’re set up on the old server.
We found that after migration, many reports didn’t work. There was an error with the MSCRM_DataSource connection. This can be reached by pointing your browser at http://newSQLServer/Reports
Choose ART_MSCRM, then click Show Details and find the MSCRM_DataSource object. Review its properties and make sure it’s configured properly. Do the same for the Subscriptions data source, as most of our custom reports use it instead of the default data source.