Mobile Windows SharePoint Services Database

Bytes

	Introduction
	Configuration and content database
	Search for database files
	Database migration

Introduction

When you use the default settings to install Microsoft Windows SharePoint Services, the installer automatically installs Microsoft SQL Server 2000 Desktop Engine (Windows) (wmsde) and uses it to create a database for your website. Windows SharePoint Services Installed in this way stores all files including databases in the "/Program Files" directory of the server computer by default.

You may need to move a database that supports Windows SharePoint Services. If the drive where the "Program Files" directory is located cannot provide sufficient disk space required by your database, you can move the database to another drive on the server computer, or move to another server with SQL Server 2000 running. To use a more powerful database maintenance tool provided by SQL Server 2000, you must move the database to another server running SQL Server 2000.

You should back up the database before migration. For information on backing up and restoring databases and websites, see the topics of "back up and restore databases using SQL Server 2000" and "back up and restore websites" in Microsoft Windows SharePoint Services administrator guide.

Note:To move the Microsoft Windows SharePoint Services Database, you must be a member of the local administrator Group of the source and target servers.

Back to Top

Configuration and content database

Windows SharePoint Services uses two databases to store all site data, including documents in the document library.Configure DatabaseIncluding all global settings of the server, whileContent DatabaseIncluding all site content, such as list items and documents. Each server can have more than one content database, but only one configuration database can. In the default installation of Windows SharePoint Services, the installer creates a configuration database named sts_config and an STS _Computer_name_ 1 content database.

Note:To find the name of your computer, enterEcho % computername %.

Back to Top

Search for database files

Each database, including the configuration database, is identified by two files:Database Files, The file extension is. MDF,Transaction Log File, The file extension is. LDF. Before moving any database, you must first obtain the path of the two files of the database. For the default Windows SharePoint services installation, the database file is located in the "/program files/Microsoft SQL Server/MSSQL $ SharePoint/Data" directory. You can find the following files in this directory:

•	Sts_config.mdf
•	Sts_config_log.ldf
•	STS _Computer_name_ 1.mdf
•	STS _Computer_name_ 1_log.ldf

If your database file is not in the default location, use the following method to obtain the full database file name.

Note:This method uses the osql command line utility. This tool allows you to run SQL statements, system processes, and script files.

Local Database Files

1.	Open the SharePoint central management page. In"Start"On the menu, point"All Programs", Click"Management Tools"And then click"SharePoint central management".
2.	To find the name of your configuration database"Server configuration"Partially click"Set database server configuration".
3.	On the "Configure Database Server Settings" page"SQL Server database name"The name of the database.
4.	To find the name of your content database, go back to the homepage of "SharePoint central management. In"Virtual Server Configuration"Partially click"Configure Virtual Server Settings". For each virtual server listed on the "virtual server list" Page, complete these steps: 1. Click the virtual server name. 2. On the "virtual server settings" page"Virtual Server Management"Part, click"Manage content database". Note the names of the listed databases. 3. If there are other virtual databases, go back to the "virtual database list" Page and repeat steps 1st and 2nd.
5.	Start the osql utility. At the Windows command prompt, type the following command: osql –E –S %computername%/SharePoint
6.	To get the name of your configuration database file, enter the following three commands at the osql command prompt and press the Enter key after each command: USE Configuration_Database_Name SELECT filename FROM sysfiles GO Each time you enter a command, the prompt value increases progressively. Write down the two displayed paths.
7.	For each content database, enter the following three commands at the osql prompt and press enter after each command: USE Content_Database_Name SELECT filename FROM sysfiles GO Each time you enter a command, the prompt value increases progressively. Write down the two paths displayed for each content database.
8.	Press Ctrl + C to exit the osql utility.

Back to Top

Database migration

Use the methods described in this section to move a database that supports Windows SharePoint services to another drive on the server computer, or to another server that has Microsoft SQL Server 2000 on the other.

Move the database to another disk drive on the same server

This method guides you to take the database offline, detach the database, move it to another drive on the same server, re-attach and restart the database.

Move the database to another hard drive

1.	At the command prompt, type the following command to stop the Web Server: iisreset /stop
2.	At the command prompt, type the following command to stop the SharePoint timer service: net stop sptimer
3.	At the command prompt, type the following command to start the osql command line utility: osql –E –S %computername%/SharePoint
4.	Type the following command to separate each database and press enter after each row: EXEC sp_detach_db 'Database_Name', 'true' GO Each time you enter a command, the prompt value increases progressively. Note:In this step, the configuration database and each content database must be executed once.
5.	Use the Windows resource browser to move each database to a new location. Migrate the database (. MDF) file and transaction log (. LDF) file of each database.
6.	In the command window for running the osql utility, type the following command to re-attach each database using the new location of these files: EXEC sp_attach_db 'Database_Name', 'd:/New_Location/Database_Name.mdf', 'd:/New_Location/Database_Name_log.LDF' GO Note:These steps must be performed once for the configuration database and each content database.
7.	Press Ctrl + C to exit the osql utility.
8.	At the command prompt, type the following command to restart the SharePoint timer service: net start sptimer
9.	At the command prompt, type the following command to restart the Web Server: iisreset /start

Move the database to a database server running Microsoft SQL Server 2000

This section describes how to move database files in the default location to another server, re-append these files, and update the configuration database to point them to a new location. Select or create a domain account before startup to connect to the SQL Server 2000 database that has been moved to the new server. To ensure security, you must grant minimum permissions to the account while ensuring that the database owner (DBO) role of the SQL Server database is provided. For more information, see Microsoft SQL Server 2000.

Note:In the following method, the original server of the database file is called the source server, and the destination of file movement is called the target server.

Database migration

1.	At the command prompt of the source server, type the following command to stop the Web Server: iisreset /stop
2.	At the command prompt on the source server, type the following command to stop the SharePoint timer service: net stop sptimer
3.	At the command prompt on the source server, type the following command to stop the wmsde Database Server: net stop mssql$sharepoint
4.	Use the Windows resource browser to move each database to a new location on the target server. Note:In this step, the configuration database and each content database must be executed once. Move the database (. MDF) file and transaction log (. LDF) file of each database.

Additional database

1.	At the Windows Command Prompt on the target server, type the following command to start the osql command line utility: osql –E –S %computername% Note:If you specify the Instance name when installing SQL Server, follow these steps to append it to % computername %: osql –E –S %computername%/Instance_Name
2.	In the command window for running osql, type the following command and press enter to append each database one by one using the new location of these files: EXEC sp_attach_db 'Database_Name', 'd:/New_Location/Database_Name.mdf', 'd:/New_Location/Database_Name,_log.LDF' GO Note:In this step, the configuration database and each content database must be executed once.
3.	Press Ctrl + C to exit the osql utility.

Assign a domain account for database operations

1.	Click on the source server"Start", Point"All Programs", Point"Management Tools"And then click"Internet Information Service (IIS) manager".
2.	In"Internet Information Service (IIS) manager"Expand the local computer and the application pool folder. All application pools of the server will be listed. By default, the application pool stsadminapppool and stsapppool1 are associated with the Windows SharePoint Services Virtual Server. Other application pools you have created for the virtual server are also listed here.
3.	Process each application pool associated with the virtual server as follows: 1. Right-click application pool and click"Attribute". 2. In"ID"Click"Configuration". 3. In"User Name"Enter the domain account of the database on the target server in the domain. This account must have the database owner (DBO) permission for the SQL Server database. Note:You can click"Pre-defined account"Reset the default account and password. 1. In"Password"Enter the domain account password in the domain, and then click"OK".
4.	Add the domain account to the iis_wpg and sts_wpg local groups on the source server.

Set the configuration database in Windows SharePoint Services

1.	On the Source Server"Internet Information Service (IIS) manager"Expand local computer, expand"Website", Right-click the SharePoint central management website, and click"Start".
2.	Open the SharePoint central management page. In"Start"On the menu, point"All Programs", Click"Management Tools"And then click"SharePoint central management".
3.	In"Server configuration"Partially click"Set database server configuration".
4.	On the "Configure database Settings" page"Database Server"Enter the name of the target server in the domain.
5.	On the "set Database Configuration" page, click"Connect to the existing configuration database"And click"OK".
6.	On the "central management" page"Server configuration"Part, click"Set Default content database server".
7.	On the "Set Default content database server" page"Database Server"Enter the target server name in the domain, and then click"OK".

Set Windows SharePoint Services content database

1.	On the "central management" page"Virtual Server Configuration"Click"Configure Virtual Server Settings".
2.	Click the link of the virtual server that contains the database to be migrated.
3.	On the "virtual server settings" page"Virtual Server Management"Click"Manage content servers".
4.	On"Content database"For each content database under, complete the following operations: 1. Click the database link. 2. On the "manage content database Settings" Page, note that"SQL Server database name"The name of the database displayed in the domain. 3. On the "manage content database Settings" Page, note that"Number of sites before alert generation"And"Maximum number of sites that can be created by this database"Set the database capacity displayed in the domain. You need to use this information when adding these databases to the target server. 4. Select"Delete content database"Check box, and then click"OK"To disconnect the database from the source server.
5.	Attaches each content database to the target server. OnEach "content database" under "content database"To complete the following operations: 1. Click"Add a content database". 2. In"Database name"Enter the name of the content database in the domain. 3. In"Number of sites before alert generation"And"Maximum number of sites that can be created by this database"Field, enter the capacity settings you saved for the content database, and then click"OK".
6.	If you want to migrate the databases of multiple virtual servers, repeat steps 2nd to 5th for each virtual server.
7.	At the command prompt on the source server, type the following command to restart the SharePoint timer service: net stop sptimer
8.	At the command prompt on the source server, type the following command to restart the Web Server: iisreset /start

Conclusion

Following the steps described in this article, you have learned how to locate a database on a server running Microsoft Windows SharePoint services and how to move it to different drives on the same server, or move to another server with Microsoft SQL Server 2000. After the database is migrated and IIS is restarted, go to your website and view the page, document library, and list to test the database in the new location.