Microsoft KB Archive/894164

= How to move the databases that are used by SharePoint Portal Server 2003 to a computer that is running SQL Server =

Article ID: 894164

Article Last Modified on 12/14/2006

-

APPLIES TO


 * Microsoft Office SharePoint Portal Server 2003

-



SUMMARY
''This step-by-step article describes how to move the databases that are used by Microsoft Office SharePoint Portal Server 2003 to a computer that is running Microsoft SQL Server. Use the procedure that is described in this article if you originally installed SharePoint Portal Server 2003 and SQL Server on the same computer, and you now want to use a remote computer that is running SQL Server for database storage. Alternatively, use the procedure that is described in this article if you originally installed SharePoint Server 2003 and SQL Server on separate computers, and you now want to use a different remote computer that is running SQL Server for database storage.

Before you can move the databases to a remote computer that is running SQL Server, you must back up SharePoint Portal Server 2003 data, delete the portal sites on the server farm, remove component assignments, and then disconnect the server from the configuration database. After you do this, you must create a new configuration database, specify server farm account settings, specify component assignments, and then restore the backup of SharePoint Portal Server 2003 data to the server.''



INTRODUCTION
This step-by-step article describes how to move the databases that are used by SharePoint Portal Server 2003 to a computer that is running SQL Server. Use the procedure that is described in this article if one of the following conditions is true:
 * You originally installed SharePoint Portal Server 2003 and SQL Server on the same computer, and you now want to use a remote computer that is running SQL Server for database storage.
 * You originally installed SharePoint Portal Server 2003 and SQL Server on separate computers, and you now want to use a different remote computer that is running SQL Server for database storage.

Back up SharePoint Portal Server 2003 data
Use the SharePoint Portal Server Data Backup and Restore tool to back up SharePoint Portal Server 2003 data. To do this, follow these steps:  Start the SharePoint Portal Server Data Backup and Restore tool. Click the Backup tab. In the Backup location and name box, specify the location where you want to store the backup files. Additionally, specify the file name to use as a prefix for the backup files. Expand Available components, and then click All server farm components. Verify that the Ready check box is displayed next to the items that you want to back up. Additionally, verify that the Ready check box is selected for each item that you want to back up. Click Backup. Click OK when you receive the message that states that the backup operation completed successfully. On the File menu, click Exit to quit the SharePoint Portal Server Data Backup and Restore tool.</li> Take screen shots of the Define Managed Paths page. To find the Define Managed Paths page, follow these steps: <ol style="list-style-type: lower-alpha;"> In Windows SharePoint Central Administration, click Configure Virtual Servers.</li> Click the first extended virtual server in the list, and then click Define Managed Paths.</li></ol>

Take screen shots of the Define Managed Paths page. Repeat this procedure for all extended virtual servers that are listed on the Configure Virtual Servers page. Make sure that you take screen shots of the Define Managed Paths page for all extended virtual servers. These screen shots will be used for reference when you re-create the managed paths after you restore the SharePoint Portal Server 2003 data.</li> Make a backup of the web.config file so that you have a list of existing safe control entries and custom Web Parts before you continue.</li></ol>

Back up the databases in SQL Server 2000
Back up the following databases in SQL Server 2000:
 * _PROF
 * _SERV
 * _SITE
 * The SharePoint Portal Server 2003 configuration database. By default, this database is named SPS_Config_db.

To do this, follow these steps:
 * 1) Create a new folder on the hard disk drive to store the databases that you back up.
 * 2) Start SQL Server Enterprise Manager.
 * 3) Expand Microsoft SQL Servers, expand the server group, expand the appropriate server, and then expand Databases.
 * 4) Right-click the database that you want to back up, point to All Tasks, and then click Backup Database.
 * 5) If a path is displayed in the Destination box, click the path, and then click Remove.
 * 6) Click Add. Click File name, and then specify the location of the folder that you created in step 1 and the file name of the backup file. Use a file name that is the same as the name of the database. For example, when you back up the   _PROF database, specify the file name as   _PROF.
 * 7) Click OK three times.
 * 8) Click OK when you receive the message that the backup operation completed successfully.
 * 9) Repeat step 4 to step 7 for each database.

Verify that the value in the PortalRecoveryBackup column of the PortalProperties table that is in the content database is not set to Null
Verify that the value in the PortalRecoveryBackup column of the PortalProperties table that is in the content database is not set to Null. If the value in the PortalRecoveryBackup column is set to Null, you cannot restore SharePoint Portal Server 2003 data from a backup. Make sure that the value is not set to Null before you follow the remaining steps in this article.

To verify that the value in the PortalRecoveryBackup column of the PortalProperties table that is in the content database is not set to Null, follow these steps:
 * 1) Start SQL Server Enterprise Manager.
 * 2) Expand Microsoft SQL Servers, expand the server group, expand the appropriate server, and then expand Databases.
 * 3) Expand the content database, and then click Tables.

Note The name of the content database typically ends in _SITE.
 * 1) Right-click PortalProperties, point to Open Table, and then click Return all rows.
 * 2) Verify that the value in the PortalRecoveryBackup column is not set to Null.

If the value in the PortalRecoveryBackup column is set to Null, use the SharePoint Portal Server Data Backup and Restore tool to create a backup of SharePoint Portal Server 2003 data. Then, repeat steps 1 through 5 to verify that the value in the PortalRecoveryBackup column is no longer set to Null. The value in the PortalRecoveryBackup column should be populated when you create a backup by using the SharePoint Portal Server Data Backup and Restore tool.

Delete the portal sites on the server farm
To delete the portal sites on the server farm, follow these steps:
 * 1) Start SharePoint Portal Server Central Administration.
 * 2) Under Portal Site and Virtual Server Configuration on the SharePoint Portal Server Central Administration for   page, click List and manage portal sites.
 * 3) Click the arrow next to the portal site, and then click Delete Portal Sites.
 * 4) Make sure that the Delete all databases check box is cleared. If the Delete all databases check box is selected, click to clear the Delete all databases check box.
 * 5) Click OK.
 * 6) Repeat steps 2 through 5 for each portal site on the server farm.
 * 7) Click Go to SharePoint Portal Server central administration on the Delete Portal Site page.

Remove e-mail server settings
If you configured an e-mail server for SharePoint Portal Server 2003, remove the e-mail server settings. To do this, follow these steps:
 * 1) Under Server Configuration on the SharePoint Portal Server Central Administration for   page, click Configure e-mail server settings.

Note To help you remember the settings on the Configure E-mail Server Settings page so that you can restore the settings later, take a screen shot of this page before you remove the settings. To do this, press ALT+PRINT SCREEN, start Microsoft WordPad or Microsoft Word, and then click Paste on the Edit menu. On the File menu, click Save, and then specify a file name and location where you want to save the file.
 * 1) Remove the Simple Mail Transfer Protocol (SMTP) server and e-mail settings, and then click OK.

Remove component assignments
To remove component assignments, follow these steps:
 * 1) Under Server Configuration on the SharePoint Portal Server Central Administration for   page, click Configure server topology.
 * 2) At the bottom of the Configure Server Topology page, click Change Components.
 * 3) In the Component Assignment area, click to clear the check boxes for each component that is currently assigned to the server. For example, click to clear the Web check box, the Search check box, and the Index check box.
 * 4) In the Job Server Component area, click None in the Job server box, and then click OK.
 * 5) Click Close.

Disconnect the server from the configuration database
To disconnect the server from the configuration database, follow these steps:
 * 1) Under Server Configuration on the SharePoint Portal Server Central Administration for   page, click Configure configuration database server.
 * 2) In the Database Connections area of the Specify Configuration Database Settings for   page, click Disconnect from configuration database, and then click OK.

Note If you receive an error message when you try to disconnect from the configuration database, follow the steps in the You receive an error message when you try to disconnect from the configuration database section.

You receive an error message when you try to disconnect from the configuration database
In certain scenarios, you receive the following error message when you try to disconnect from the configuration database:

You cannot remove this server because some components are still active. Deactivate these components on the Configure server topology and component assignments page and then try again.

To resolve this issue, follow these steps: <ol> Click SharePoint Portal Server in the left pane, and then click Configure server topology under Server Configuration on the SharePoint Portal Server Central Administration for  page.</li> Under Database Server Settings, verify that the Global e-mail server entry and the Single sign-on credentials entry are not configured.</li> Under Component Assignments, verify that no components are assigned to the server. Click Close.</li> Repeat the steps in the Disconnect the server from the configuration database section to disconnect the server from the configuration database. Then, use one of the following methods: <ul> If you can disconnect, follow the steps in the Configure server farm account settings section.</li> If you cannot disconnect, go to step 5.</li></ul> </li> Specify the component settings database server and the content database server. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Click SharePoint Portal Server in the left pane, and then click Configure server topology under Server Configuration on the SharePoint Portal Server Central Administration for  page.</li> Next to the Component settings database server entry under Database Server Settings, click the name of the server.</li> Click Go to SharePoint Portal Server central administration on the Operation Successful page.</li> Click to clear the Use same server as configuration database check box on the Specify Settings for Component Settings Database page, type the name of the new server in the Server name box, and then click OK.</li> Under Server Configuration on the SharePoint Portal Server Central Administration for  page, click Configure server topology.</li> <li>Next to the Content database server entry under Database Server Settings, click the name of the server.</li> <li>Click to clear the Use same server as configuration database check box on the Specify Content Database page, type the name of the new server in the Server name box, and then click OK.</li> <li>Click Go to SharePoint Portal Server central administration on the Operation Successful page.</li> <li>Repeat the steps in the Disconnect the server from the configuration database section to disconnect the server from the configuration database. Then, use one of the following methods: <ul> <li>If you can disconnect, follow the steps in the Specify the content access account and portal site application pool identity section.</li> <li>If you cannot disconnect, go to step 6.</li></ul> </li></ol> </li> <li>Use SQL Server Enterprise Manager to detach the configuration database. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Start SQL Server Enterprise Manager.</li> <li>Expand Microsoft SQL Servers, expand the server group, expand the appropriate server, and then expand Databases.</li> <li>Right-click the configuration database, point to All Tasks, and then click Detach Database.</li> <li>Click OK in the Detach Database -  dialog box.

The database is detached and is copied to the :\Program Files\Microsoft SQL Server\MSSQL\Data folder.</li> <li>Click OK when you receive the message that states that the operation completed successfully.</li> <li>Repeat the steps in the Disconnect the server from the configuration database section to disconnect the server from the configuration database.</li></ol> </li></ol>

Specify the content access account and the portal site application pool identity
To specify the content access account and the portal site application pool identity, follow these steps: <ol> <li>Specify the default content access account. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Default Content Access Account area of the Configure Server Farm Account Settings page, click to select the Specify account check box.</li> <li>In the User name (DOMAIN\user name) box, type the user account that you want to use as the default content access account. Type the user account in \  format. Specify the password in the Password box and in the Confirm Password box.</li></ol> </li> <li>Specify the portal site application pool identity. To do this, type the user name that you want to use as the portal site application pool identity in the User name (DOMAIN\user name) box in the Portal Site Application Pool Identity area. Type the user name in \  format. Specify the password in the Password box and in the Confirm Password box.</li> <li>Click OK.</li></ol>

Create the configuration database
To create the configuration database on the new database server, follow these steps:
 * 1) In the Database Connections area of the Specify Configuration Database settings for   page, click Create configuration database.
 * 2) In the Configuration Database Server area, type the name of the remote computer that is running SQL Server in the Database server box.
 * 3) In the Configuration Database Name area, specify the name that you want to use for the configuration database, and then click OK.

Configure server farm account settings
To configure server farm account settings, follow these steps:
 * 1) In the Contact E-mail Address area of the Configure Server Farm Account Settings page, type your e-mail address.
 * 2) In the Proxy Server Settings area, verify that Do not connect by using a proxy server is selected. Then, click OK.

Specify component assignments
To specify component assignments, follow these steps: <ol> <li>At the bottom of the Configure Server Topology page, click Change Components.</li> <li>On the Change Component Assignments page, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Component Assignment area, click to select the check boxes that are next to the components that you want to assign to the server. For example, click to select the Web check box, the Search check box, and the Index check box.</li> <li>In the Job server box in the Job Server Component area, click the name of the server.</li> <li>Click OK, and then click Close.</li></ol> </li> <li>If you want to configure an e-mail server, click Configure e-mail server settings under Server Configuration on the SharePoint Portal Server Central Administration for  page.</li> <li>On the Configure E-Mail Server Settings page, specify the SMTP server and e-mail addresses that you want to use. Then, click OK.</li></ol>

Restore SharePoint Portal Server 2003 data
To restore SharePoint Portal Server 2003 data from a backup, follow these steps: <ol> <li>Start the SharePoint Portal Server Data Backup and Restore tool.</li> <li>Click the Restore tab.</li> <li>Click Browse next to the Manifest file box.</li> <li>Locate the folder that contains the backup files, click the .xml manifest file, and then click Open.</li> <li>Expand Available components, and then click All server farm components.</li> <li>Click the SITEDBS item, and then click Edit.

Note Do not click to select the Parameters needed check box.</li> <li>In the Portal recovery information dialog box, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the IIS virtual server and portal Url area, verify that the following conditions are true: <ul> <li>The Microsoft Internet Information Services (IIS) virtual server is set to Default Web Site.</li> <li>The portal URL is set to the URL that you want. For example, the URL is set to http:// .</li></ul> </li> <li>In the Database information area, click the name of the database server. Then, specify the names that you want to use for the content database, the user profile database, and the services database.

Note The name of the content database typically ends in _SITE. The name of the user profile database typically ends in _PROF. The name of the services database typically ends in _SERV.</li> <li>Click OK.</li></ol> </li> <li>Verify that the Ready check box is displayed for the SITEDBS item, and then click to select the Ready check box.

Note When you move databases to a remote computer that is running SQL Server, you do not have to restore the index. However, if you want to restore the index, make sure that the Ready check box that is next to the INDEX item is selected.</li> <li>Click Restore.</li> <li>Click OK when you receive the message that states that the restore operation completed successfully.</li> <li>On the File menu, click Exit to quit the SharePoint Portal Server Data Backup and Restore tool.</li> <li>From Windows SharePoint Services Central Administration, click Configure Virtual Servers. For each extended site, click Define Managed Paths, and then re-create the managed paths by using the reference screen shots that you took during backup.</li> <li>Restore the backup of the web.config file to re-create safe control entries and custom Web Parts entries.</li></ol>

<div class="references_section">