How to move Microsoft Dynamics CRM 3.0 SQL databases from one server to a different server that is in the same domain

Notes

• We recommend that you use this procedure to move Microsoft CRM SQL databases from one server to a different server that is in the same domain. You must not use this procedure to move data from one server to another server across domains.
• Use this procedure to move data from a named instance of Microsoft SQL Server to a default instance of SQL Server that is on the same computer.
• You do not have to make any changes on the Microsoft CRM laptop or desktop clients for Microsoft Office Outlook when you want to move Microsoft CRM SQL databases from one server to a different server that is in the same domain.

Important Make sure that you read and understand this whole article before you try to complete this procedure.

Prepare to move the databases

Before you move the databases, make sure that the following preparations are completed:

• All users are logged out of Microsoft CRM.
• All Microsoft CRM client for Outlook users have synchronized their offline data to Microsoft CRM by going online.
• You have a current backup of all Microsoft CRM SQL databases.

Move the databases

After you verify that the preparations are completed, follow these steps to move the Microsoft CRM SQL databases:

1. Make a backup of the following primary Microsoft CRM SQL databases:
   • OrganizationName_METABASE
   • OrganizationName_MSCRM
   To back up these databases, follow these steps:
   1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
   2. In SQL Server Management Studio, click a server in the Server name list, and then click Connect.
   3. Expand the Databases folder.
   4. Right-click one of the two databases, point to Tasks, and then click Back Up.
   5. From the Backup type list, select Full, and then click Add in the Destination section.
   6. In the Select Backup Destination dialog box, click the Browse button (...).
   7. In the Locate Database Files dialog box, enter a name for the backup file, and then click OK.
   8. Continue to click OK until all dialog boxes are closed. You will receive a message that states that the backup is completed successfully.
   9. Repeat steps a through h for the other database. Do not delete anything from the original instance of Microsoft SQL Server. If you leave the files on the original instance of SQL Server, you can use the original setup as a reference when you perform step 5.
2. Transfer the backup files that you created in step 1 to the new instance of SQL Server.
3. Create the two databases into which you will restore the backup files. To do this, follow these steps:
   1. Start SQL Server Management Studio.
   2. Expand the Databases folder.
      
   3. Right-click Databases. Then click New Database.
   4. Enter a name for the database. For example, type OrganizationName_MSCRM.
   5. Click OK.
      
      Important Make sure that you use the same naming syntax that was used on the original instance of Microsoft SQL Server.
   6. Repeat steps c through e to create the OrganizationName_METABASE database. Again, make sure that you use the same naming syntax that was used on the original instance of Microsoft SQL Server.
4. Restore the backup files. To do this, follow these steps:
   1. Start SQL Server Management Studio.
   2. Expand the Databases folder.
   3. Right-click the OrganizationName_MSCRM database, point to Tasks, point to Restore, and then click Database.
   4. In the Source for restore area, select the From device option, and then click the Browse button (...).
   5. In the Specify Backup dialog box, click Add.
   6. In the Locate Backup File dialog box, click the location of the backup file for the OrganizationName_MSCRM database, and then click OK.
   7. In the Specify Backup dialog box, click OK.
   8. In the Restore Database dialog box, click Restore Source, click Options, and then click to select the Overwrite check box to overwrite the existing database.
   9. In the Restore Database File As area, examine the paths for the two physical database files. Make sure that these paths point to valid locations. If the paths do not point to valid locations, modify the paths appropriately.
   10. In the Restore Database dialog box, click OK to start the restore process.
       
       Note Depending on the size of the database, this process may take a long time. When the process is complete, you will receive a message that states that the restore operation completed successfully.
   11. Repeat steps c through j for the OrganizationName_METABASE database.
5. Set up the security groups by mapping to the Microsoft Windows NT security groups. To do this, follow these steps.
   
   Note As you follow these steps, use the security folder that is on the original instance of SQL Server as a reference.
   1. Start SQL Server Management Studio. Then expand the Security folder.
   2. Right-click Logins. Then click New Login.
   3. In the Login dialog box, click General on the left pane, and then click Search.
   4. In the Select User or Group dialog box, click Object Types, make sure that the Groups check box is selected, and then click OK.
      
   5. Click Locations, click the folder to select all the folder contents, and then click OK.
   6. In the Enter the object name to select field, type SQL, and then click Check Names.
   7. Select the SQLAccessGroup {GUID} record. Then click OK.
   8. In the Login dialog box, click the Windows authentication option, leave all the other default options at their current settings, and then click OK to close all the dialog boxes.
6. Modify the Microsoft CRM System DSN ODBC Data Source on the Microsoft CRM server to point to the new server that is running Microsoft SQL server. To do this, follow these steps:
   1. Click Start, point to Administrative Tools, and then click Data Sources (ODBC).
   2. In the ODBC Data Source Administrator dialog box, click the System DSN tab, click Microsoft CRM in the System Data Sources list, and then click Configure.
   3. In the first Microsoft SQL Server DSN Configuration window, locate the Server list that contains the names of computers that are running SQL Server. Click the server that you want to connect to.
   4. Click Next three times to configure the data source.
   5. Click Finish.
   6. In the ODBC Microsoft SQL Server Setup window, click Test Data Source to validate the connection to the server.
   7. Click OK two times to complete the configuration.
   8. Click OK to close the ODBC Data Source Administrator dialog box.
      
7. Reconfigure the Microsoft Dynamics CRM server. To do this, follow these steps:
   1. On the Microsoft Dynamics CRM server, click Start, point to All Programs, point to Microsoft CRM, and then click Deployment Manager.
   2. In the left pane, click Server Manager, right-click the Microsoft Dynamics CRM server, and then click Configure SQL Server.
   3. In the Microsoft Dynamics CRM server window, select the new computer that is running Microsoft SQL Server from the SQL Server list, and then click Next.
   4. In the Microsoft CRM database list, click the appropriate Microsoft CRM SQL database. If you have multiple Microsoft CRM servers, click to select the Apply changes to all servers in the deployment check box.
   5. Click Finish.
8. Test Microsoft CRM by verifying that you can access the data. Additionally, test the Microsoft Dynamics CRM clients for Outlook to make sure that you can use the program.
9. Verify that the SQL Server jobs have been created. To do this, follow these steps:
   1. Start SQL Server Management Studio.
   2. Expand SQL Server Agent, and then expand the Jobs folder.
   3. Verify that the following jobs are listed in the Jobs folder:
      • Organization_Name.Update Contract States
      • MSCRM Identity Reseeding
      • MSCRM Index Reindexing
      • MSCRM Stored Procedures Priming
      • Start_Incremental on Organization_Name_ftcat_documentindex
   4. If the jobs that are listed in step c are not listed in the Jobs folder, follow the steps in the following Microsoft Knowledge Base article:
      910044 When you move Microsoft Dynamics CRM databases to a new instance of SQL Server, SQL Server jobs may not be created
      
      
10. Clean up the contents of the original computer that is running Microsoft SQL Server. To do this, follow these steps:
    1. Delete the old Microsoft CRM SQL databases.
    2. Delete the following login groups:
       • SQLAccessGroup
       • UserGroup
       • ReportingGroup

Note After you successfully move Microsoft CRM SQL databases from one server to a different server, remember to schedule regular backups of the databases that are on the new server.