Moving Windows SharePoint Services Databases

Published: November 1, 2003

By Bryan Jeffries, Microsoft Corporation

Learn how to move the databases used by Windows SharePoint Server to a different drive on the same server or to a separate server running Microsoft SQL Server 2000. You do not need to be familiar with Microsoft SQL Server tools to use this document.

On This Page

Introduction
About the Configuration and Content Databases
Locating the Database Files
Moving the Databases

Introduction

When you install Microsoft® Windows® SharePoint™ Services by using the default settings, the setup program automatically installs Microsoft SQL Server™ 2000 Desktop Engine (Windows) (WMSDE) and uses it to create the databases for your Web sites. Installing Windows SharePoint Services in this way puts all the files, including the database, in your server computer's \Program Files directory by default.

You may want to move the databases that support Windows SharePoint Services. If the drive containing the Program Files directory does not have enough disk space for your database requirements, move the databases to another drive on your server computer or to a separate server running SQL Server 2000. If you want to take advantage of the more powerful database maintenance tools provided by SQL Server 2000, you must move your databases to a server running SQL Server 2000.

You should back up your databases before moving them. For information about backing up and restoring databases and Web sites, see the topics “Backing Up and Restoring Databases by Using the SQL Server 2000 Tools” and “Backing Up and Restoring Web Sites” in the Microsoft Windows SharePoint Services Administrator's Guide.

Note: To move Microsoft Windows SharePoint Services databases, you must be a member of the local Administrators group on the server from which you are moving the database files and on the server to which you are moving the files.

About the Configuration and Content Databases

Windows SharePoint Services uses two databases to store all site data, including documents in document libraries. The configurationdatabase contains all of the global settings for the server, and the content database contains all of the site content, such as list items and documents. There can be more than one content database per server, but there can be only one configuration database. In a default installation of Windows SharePoint Services, setup creates a configuration database named STS_Config and a content database named STS_Computer_Name_1.

Note: To find the name of your computer, type echo %computername% at the command prompt.

Locating the Database Files

Each database, including the configuration database, is identified by two files: the database file, which has a .mdf filename extension, and the transaction log file, which has a .ldf extension. Before moving each database, you must get the paths to these two files for that database. If you have a default Windows SharePoint Services installation, the database files are in the \Program Files\ Microsoft SQL Server\MSSQL$SHAREPOINT\Data directory. You will find the following files in that directory:

• STS_Config.mdf

• STS_Config_log.LDF

• STS_Computer_Name_1.mdf

• STS_Computer_Name_1_log.LDF

If your database files are not in the default location, use the following procedure to get the full database filenames.

Note: This procedure uses the OSQL command line utility, which lets you run SQL statements, system procedures, and script files.

Locate database files

1. Open the SharePoint Central Administration Pages.

   On the Start menu, point to All Programs, click Administrative Tools, and then click SharePoint Central Administration.

2. To find the name of your configuration database, in the Server Configuration section, click Set configuration database server.

3. On the Set Configuration Database Server page, in the SQL Server database name field, note the database name.

4. To find the names of your content databases, go back to the home page of the SharePoint Central Administration pages. In the Virtual Server Configuration section, click Configure virtual server settings.

   For each virtual server that is listed in the Virtual Server List page, do the following:

   1. Click the virtual server name.

   2. On the Virtual Server Settings page, in the Virtual Server Management section, click Manage content databases.

      Note the database names that are listed.

   3. If there are more virtual servers, go back to the Virtual Server List page and repeat steps 1 and 2.

5. Start the OSQL utility. At a Windows command prompt, type the following:

osql –E –S %computername%\SharePoint

6. To get the filenames for your configuration database, type the following three steps at the OSQL prompt, pressing ENTER after each line:

USE Configuration_Database_Name
SELECT filename FROM sysfiles GO

The prompt will increment in value as you enter each command. Write down the two paths that are displayed.

7. For each content database, type the following three steps at the OSQL prompt, pressing ENTER after each line:

USE Content_Database_Name
SELECT filename FROM sysfiles GO

The prompt will increment in value as you enter each command. Write down the two paths that are displayed for each content database.

8. Exit the OSQL utility by pressing CTRL+C.

Moving the Databases

Use the procedures in this section to move the databases that support Windows SharePoint Services to another drive on your server computer or to a separate server running Microsoft SQL Server 2000.

Moving the Databases to a Different Hard Disk Drive on the Same Server

This procedure guides you through taking each database offline, detaching it, moving it to a different drive on the same server, reattaching it, and restarting it.

Move databases to a different hard disk drive

1. Stop the Web server by typing the following at the command prompt:

iisreset /stop

2. Stop the SharePoint Timer Service by typing the following at the command prompt:

net stop sptimer

3. At the command prompt, launch the OSQL command line utility by typing the following:

osql –E –S %computername%\SharePoint

4. To detach each database, type the following, pressing ENTER after each line:

EXEC sp_detach_db 'Database_Name', 'true' GO

The prompt will increment in value as you enter each command.

**Note:** This step must be followed once for your configuration database and once for each content database.

5. By using Windows Explorer, move each database to its new location. For each database, move the database (.mdf) file and the transaction log (.ldf) file.

6. In the command window running the OSQL utility, reattach each database using the new location for the files, by typing the following:

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 followed once for your configuration database and once for each content database.

7. Exit the OSQL utility by pressing CTRL+C.

8. Restart the SharePoint Timer Service by typing the following at the command prompt:

net start sptimer

9. Restart the Web server by typing the following at the command prompt:

iisreset /start

Moving the Databases to a Database Server Running Microsoft SQL Server 2000

The procedures in this section guide you through moving your database files from the default location to a separate server, reattaching them, and updating the configuration database to point to the new location. Before starting, choose or create a domain account to use to connect to the SQL Server 2000 databases after they are moved to the new server. To help protect security, give this account the minimum privileges that will support it having the database owner (dbo) role on the SQL Server database. For details, see the Microsoft SQL Server 2000 documentation.

Note: In the following procedure, the server from which you are moving the database files is called the source server and the server to which you are moving the files is called the destination server.

Move the databases

1. On the source server, stop the Web server by typing the following at the command prompt:

iisreset /stop

2. On the source server, stop the SharePoint Timer Service by typing the following at the command prompt:

net stop sptimer

3. On the source server, stop the WMSDE database server by typing the following at the command prompt:

net stop mssql$sharepoint

4. By using Windows Explorer, move each database to the new location on the destination server.

   Note: This step must be followed once for your configuration database and once for each content database. For each database, move the database (.mdf) file and the transaction log (.ldf) file.

Attach the databases

1. On the destination server, at a Windows command prompt, launch the OSQL command line utility by typing the following:

osql –E –S %computername%

**Note:** If you specified an instance name when installing SQL Server, append it to %computername% as follows:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">

osql –E –S %computername%\Instance_Name

2. In the command window running OSQL, attach each database using the new location for the files by typing the following and pressing ENTER after each line:

EXEC sp_attach_db 'Database_Name', 'd:\New_Location\Database_Name.mdf',
'd:\New_Location\Database_Name,_log.LDF' GO

**Note:** This step must be followed once for your configuration database and once for each content database.

3. Exit the OSQL utility by pressing CTRL+C.

Assign a domain account under which the databases will run

1. On the source server, click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.

2. In Internet Information Services (IIS) Manager, expand the local computer and expand the Application Pools folder.

   All application pools for the server are listed. By default, the application pools STSAdminAppPool and STSAppPool1 are associated with Windows SharePoint Services virtual servers. If you have created other application pools for virtual servers, they will be listed here.

3. For each application pool associated with a virtual server, do the following:

   1. Right click the application pool, and then click Properties.

   2. On the Identity tab, click Configurable.

   3. In the User name field, enter the domain account under which the databases will run on the destination server. This same account must have database owner (dbo) privileges in the SQL Server database.

   Note: You can reset the default account and password by clicking Predefined.

   1. In the Password field, enter the password for the domain account, and then click OK.

4. On the source server, add the domain account to the IIS_WPG and STS_WPG local groups.

Set the configuration database in Windows SharePoint Services

1. On the source server, in Internet Information (IIS) Services Manager, expand the local computer, expand Web Sites, right-click the SharePoint Central Administration Web site, and click Start.

2. Open the SharePoint Central Administration Pages.

   On the Start menu, point to All Programs, click Administrative Tools, and then click SharePoint Central Administration.

3. In the Server Configuration section, click Set configuration database server.

4. On the Set Configuration Database page, in the Database server field, enter the name of the destination server.

5. On the Set Configuration Database page, click Connect to existing configuration database, and click OK.

6. On the Central Administration page, in the Server Configuration section, click Set default content database server.

7. On the Set Default Content Database Server page, in the Database server field, enter the name of the destination server, and then click OK.

Set the content database in Windows SharePoint Services

1. On the Central Administration page, under Virtual Server Configuration, click Configure virtual server settings.

2. Click the link for the virtual server containing the databases that you are moving.

3. On the Virtual Server Settings page, under Virtual Server Management, click Manage content databases.

4. For each content database listed under Content Databases, do the following:

   1. Click the database link.

   2. On the Manage Content Database Settings page, note the database name as displayed in the SQL Server database name field.

   3. On the Manage Content Database Settings page, note the database capacity settings, as displayed in the Number of sites before a warning message is generated and the Maximum number of sites that can be created in this database fields. You will need this information when adding the databases to the destination server.

   4. Disconnect the database from the source server by selecting the Remove content database check box, and then click OK.

5. Attach each content database to the destination server. For each content database listed under Content Databases, do the following:

   1. Click Add a content database.

   2. In the Database Name field, type the name of the content database.

   3. In the Number of sites before a warning message is generated and the Maximum number of sites that can be created in this database fields, enter the capacity settings information you saved for the content database, and click OK.

6. If you are moving databases for more than one virtual server, repeat steps 2 through 5 for each additional virtual server.

7. On the source server, restart the SharePoint Timer Service by typing the following at the command prompt:

net start sptimer

8. On the source server, restart the Web server by typing the following at the command prompt:

iisreset /start

Conclusion

By following the steps in this document, you have learned how to locate the databases on a server running Microsoft Windows SharePoint Services and move them to a different drive on the same server or to a separate server running Microsoft SQL Server 2000. After you have moved the databases and restarted IIS, test the databases in their new locations by browsing to your Web sites and viewing pages, document libraries, and lists.