SharePoint Portal Server 2003数据库迁移指南
简单来说，就是首先使用SPS2003备份和恢复工具将SPS2003门户网站完整的备份下来，然后在SPS2003上删除全部原有门户，然后更新配置数据库到新的SQL Server服务器上，然后使用SPS2003备份和恢复工具来恢复门户网站，这
SPS2003的数据库迁移应该是一个常见的维护操作。比如，在我们安装SPS2003时，很可能将其使用的SQL Server也一起安装在同一台服务器上，但是随着时间的流逝，SPS2003门户网站里面的内容越来越多，访问量越来越大，SQL Server的负荷也会越来越重。这个时候，SPS管理员就可能需要认真考虑一下是否将SQL Server服务器迁移到另一台性能更好的服务器上。这样，就带来了SPS2003数据库迁移的问题。
微软对于如何将正在运行的SPS2003的数据库进行迁移，有标准的解决方案。简单来说，就是首先使用SPS2003备份和恢复工具将SPS2003门户网站完整的备份下来，然后在SPS2003上删除全部原有门户，然后更新配置数据库到新的SQL Server服务器上，然后使用SPS2003备份和恢复工具来恢复门户网站，这样，门户网站的内容数据库就也迁移到新的SQL Server服务器上了。
微软标准解决方案的具体步骤，请参看微软站点上的《How to move the databases that are used by SharePoint Portal Server 2003 to a computer that is running SQL Server》一文。
除了这个官方解决方案之外，我在这里还看到过一个非官方的方案，很有意思，而且要比官方方案要更简捷一些。我将这个方案整理了出来，您可以直接参考：
1、将SPS使用的三个数据库全部完整备份下来（这三个数据库的名字分别以_Prof, _Serv和_Site结尾）。这个步骤非常非常重要，保存好备份后的文件！！
2、在新的服务器上安装好SQL Server，然后以同样的名字创建那三个数据库，使用SQL Server的恢复数据库功能将备份好的数据库恢复到这台服务器上。注意，备份/恢复数据库的操作，可能并不会将原有的安全权限信息完整复制到新的服务器上，请手工操作，确保新的SQL Server服务器上的安全设置与原有的保持一致。
3、备份好SPS门户网站对应磁盘根目录下的web.config文件。
（以下的步骤会使SPS门户站点暂时不可用，建议干脆暂时停止用户对SPS门户站点的访问）
4、打开SharePoint Portal Server管理中心。
5、进入“指定组件设置数据库服务器”，勾掉“使用与配置数据库相同的服务器”复选框，在下面的“服务器名称”文本框中填入新SQL Server服务器名称，确定。
6、进入“指定内容数据库服务器”，勾掉“使用与配置数据库相同的服务器”复选框，在下面的“服务器名称”文本框中填入新SQL Server服务器名称，确定。
7、进入“列表和管理门户网站”，选择相应的门户网站，删除它，在删除确认页面，勾选“删除所有数据库”。
8、点击管理中心左侧的“Windows SharePoint Services”，进入到WSS管理中心。
9、进入“设置服务器配置数据库”，在“数据库服务器”文本框中填入新的SQL Server服务器的名称，确定。
10、点击左侧的“SharePoint Portal Server”，回到SPS管理中心。
11、进入“配置服务器拓扑结构”，确认在“数据库服务器设置”区域下面的所有数据库服务器设置都使用了新的SQL Server服务器名称，确认在“组件分配”区域下面，Web、搜索、索引、作业这几个服务器角色仍然是使用的当前的Web服务器。点击“关闭”按钮。
12、进入“列表和管理门户网站”，点击“恢复门户网站”，在恢复门户网站页面填入新的SQL Server服务器的名称和其他相关信息，确定。
13、将第3步备份的web.config拷贝回SPS门户网站对应的磁盘根目录下。如果web.config里面有对原来SQL Server服务器的引用，那么将这些引用更新到指向新的SQL Server服务器。
14、打开SPS门户网站，确认数据库迁移已经成功了。在门户网站的新闻区域，可能会增加一条新的新闻列表，把它删除即可。
最后要提醒的是，不管你是使用哪种方案来做数据库迁移，之前一定要先做好备份的两个步骤：第一个步骤是使用SPS2003备份和恢复工具，对整个门户网站进行完整备份，第二个步骤是使用SQL Server的数据库备份来将SPS2003所使用的所有数据库进行完整备份。
来源：http://blog.joycode.com/kaneboy/archive/2006/10/24/85613.aspx
How to move the databases that are used by SharePoint Portal Server 2003 to a computer that is running SQL Server【KB894164】
http://support.microsoft.com/default.aspx?scid=kb;en-us;894164
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.
SharePoint Portal Server 2003 SP2 had a code defect in the SPS backup and restore utility when trying to restore a backup to a SQL Server 2005 server with full text indexing enabled. This issue has been addressed in the SharePoint Portal Server 2003 SP3 and is detailed in the article: http://support.microsoft.com/kb/919175 (http://support.microsoft.com/kb/919175) It is advised to have the SharePoint environment at SP3 or at least have this fix applied before trying to move databases for SharePoint.
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:
1. Start the SharePoint Portal Server Data Backup and Restore tool.
2. Click the Backup tab.
3. 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.
4. 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.
5. Click Backup.
6. Click OK when you receive the message that states that the backup operation completed successfully.
7. On the File menu, click Exit to quit the SharePoint Portal Server Data Backup and Restore tool.
8. Take screen shots of the Define Managed Paths page. To find the Define Managed Paths page, follow these steps:
a. In Windows SharePoint Central Administration, click Configure Virtual Servers.
b. Click the first extended virtual server in the list, and then click Define Managed Paths.
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.
9. 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.
Back up the databases in SQL Server 2000
Back up the following databases in SQL Server 2000:
- PortalSiteName _PROF
- PortalSiteName _SERV
- PortalSiteName _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 PortalSiteName _PROF database, specify the file name as PortalSiteName _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.
4. Right-click PortalProperties, point to Open Table, and then click Return all rows.
5. 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 ServerName 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 ServerName 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.
2. 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 ServerName 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 ServerName page, click Configure configuration database server.
2. In the Database Connections area of the Specify Configuration Database Settings for ServerName 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:
1. 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 ServerName page.
2. Under Database Server Settings, verify that the Global e-mail server entry and the Single sign-on credentials entry are not configured.
3. Under Component Assignments, verify that no components are assigned to the server. Click Close.
4. 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:
o If you can disconnect, follow the steps in the Configure server farm account settings section.
o If you cannot disconnect, go to step 5.
5. Specify the component settings database server and the content database server. To do this, follow these steps:
a. 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 ServerName page.
b. Next to the Component settings database server entry under Database Server Settings, click the name of the server.
c. Click Go to SharePoint Portal Server central administration on the Operation Successful page.
d. 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.
e. Under Server Configuration on the SharePoint Portal Server Central Administration for ServerName page, click Configure server topology.
f. Next to the Content database server entry under Database Server Settings, click the name of the server.
g. 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.
h. Click Go to SharePoint Portal Server central administration on the Operation Successful page.
i. 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:
§ If you can disconnect, follow the steps in the Specify the content access account and portal site application pool identity section.
§ If you cannot disconnect, go to step 6.
6. Use SQL Server Enterprise Manager to detach the configuration database. To do this, follow these steps:
. Start SQL Server Enterprise Manager.
a. Expand Microsoft SQL Servers, expand the server group, expand the appropriate server, and then expand Databases.
b. Right-click the configuration database, point to All Tasks, and then click Detach Database.
c. Click OK in the Detach Database - DatabaseName dialog box.
The database is detached and is copied to the Drive:\Program Files\Microsoft SQL Server\MSSQL\Data folder.
d. Click OK when you receive the message that states that the operation completed successfully.
e. Repeat the steps in the Disconnect the server from the configuration database section to disconnect the server from the configuration database.
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:
1. Specify the default content access account. To do this, follow these steps:
a. In the Default Content Access Account area of the Configure Server Farm Account Settings page, click to select the Specify account check box.
b. 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 DomainName\UserName format. Specify the password in the Password box and in the Confirm Password box.
2. 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 DomainName\UserName format. Specify the password in the Password box and in the Confirm Password box.
3. Click OK.
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 ServerName 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:
1. At the bottom of the Configure Server Topology page, click Change Components.
2. On the Change Component Assignments page, follow these steps:
a. 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.
b. In the Job server box in the Job Server Component area, click the name of the server.
c. Click OK, and then click Close.
3. 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 ServerName page.
4. On the Configure E-Mail Server Settings page, specify the SMTP server and e-mail addresses that you want to use. Then, click OK.
Restore SharePoint Portal Server 2003 data
To restore SharePoint Portal Server 2003 data from a backup, follow these steps:
1. Start the SharePoint Portal Server Data Backup and Restore tool.
2. Click the Restore tab.
3. Click Browse next to the Manifest file box.
4. Locate the folder that contains the backup files, click the .xml manifest file, and then click Open.
5. Expand Available components, and then click All server farm components.
6. Click the SITEDBS item, and then click Edit.
Note Do not click to select the Parameters needed check box.
7. In the Portal recovery information dialog box, follow these steps:
a. In the IIS virtual server and portal Url area, verify that the following conditions are true:
§ The Microsoft Internet Information Services (IIS) virtual server is set to Default Web Site.
§ The portal URL is set to the URL that you want. For example, the URL is set to http:// ServerName.
b. 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.
c. Click OK.
8. 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.
9. Click Restore.
10. Click OK when you receive the message that states that the restore operation completed successfully.
11. On the File menu, click Exit to quit the SharePoint Portal Server Data Backup and Restore tool.
12. 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.
13. Restore the backup of the web.config file to re-create safe control entries and custom Web Parts entries.
For more information about how to move an installation of SharePoint Portal Server 2003 that uses Microsoft SQL Server Desktop Engine 2000 (MSDE) to SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
837848 (http://support.microsoft.com/kb/837848/ ) How to move SharePoint Portal Server 2003 from MSDE to SQL Server
For more information about how to back up and restore SharePoint Portal Server 2003, see the "Backup and Restore" topic in the Microsoft Office SharePoint Portal Server 2003 Administrator's Guide. The Microsoft Office SharePoint Portal Server 2003 Administrator's Guide (Administrator's Help.chm) is located in the Docs folder in the root folder of the SharePoint Portal Server 2003 CD.