Microsoft KB Archive/843580

Knowledge Base

How to change the location of the Windows SharePoint Services database files

Article ID: 843580

Article Last Modified on 5/29/2007

---

APPLIES TO

• Microsoft Windows SharePoint Services
• Microsoft Office SharePoint Server 2007

---

INTRODUCTION

This article describes how to move the Microsoft SQL Server Desktop Engine (MSDE) databases that are used by Microsoft Windows SharePoint Services.

MORE INFORMATION

By default, Windows SharePoint Services uses one database for configuration and another database for content. By default, the MSDE databases are located on the hard disk drive where Microsoft Windows is installed. The hard disk drive where Windows is installed is typically drive C. To move the MSDE databases to a different folder on you Windows SharePoint Services server, follow these steps:

1. Locate the two MSDE database files STS_Config.mdf and STS_ServerName_1.mdf, where ServerName is the name of your server that is hosting the content database. By default, these files are located in the following folder:

   C:\Program Files\Microsoft SQL Server\MSSQL$Sharepoint\Data

2. Click Start, click Run, type cmd, and then press ENTER.
3. Start the Osql.exe tool. To do this, type osql -E -S ServerName\InstanceName at the command prompt, where ServerName is the name of the server where the MSDE database files are located, and where InstanceName is the name of the instance of MSDE, and then press ENTER.
   
   Note By default, the instance name is sharepoint.
4. At the osql command prompt, back up the existing configuration and content databases. To do this, type the following at the osql command prompt

   BACKUP DATABASE sharepoint TO DISK = 'PathName'

   where sharepoint is the name of the MSDE database, and where PathName is the path and file name that you want, such as C:\MSDE\Backup\sharepoint.bak.
5. Type GO, and then press ENTER.
6. Repeat steps 4 and 5 for each database.
7. Detach the configuration database. To do this, type EXEC sp_detach_db 'Configurationdb' at the command prompt, where Configurationdb is the name of the MSDE configuration database, and then press ENTER. For example, type EXEC sp_detach_db ‘STS_Config’, and then press ENTER.
8. Detach the content database. To do this, type EXEC sp_detach_db “Contentdb” at the command prompt, where Contentdb is the name of the MSDE content database, and then press ENTER. For example, type EXEC sp_detach_db ‘STS_ServerName_1', and then press ENTER.
   
   Note If you receive a message that the database is in use and cannot be detached, start a new command prompt. At the new command prompt, type the following, and then press ENTER after each line:

   net pause mssql$sharepoint
   net continue mssql$sharepoint

9. Start another command prompt, or start Microsoft Windows Explorer, and then copy both of the MSDE database files that you located in step 1 to the new folder that you want.

10. Attach the configuration database. To do this, type the following at the command prompt where the Osql.exe tool is running

    EXEC sp_attach_db @dbname = N'STS_Config', @filename1 = N'C:\new_database_location\STS_Config.mdf', @filename2 = N'C:\new_database_location\STS_Config_log.ldf'

    where new_database_location is the folder where you moved the configuration database files in step 9.

11. Attach the content database. To do this, type the following at the command prompt where the Osql.exe tool is running

    EXEC sp_attach_db @dbname = N'STS_spssrvlc_1', @filename1 = N'C:\new_database_location\STS_ServerName_1.mdf', @filename2 = N'C:\new_database_location\STS_ServerName_1_log.ldf'

    where wherenew_database_location is the folder where you moved the content database files in step 9.

12. At the command prompt, type quit, and then press ENTER to quit the Osql.exe tool.

REFERENCES

For more information about how to perform backup and restore operations in Windows SharePoint Services, click the following article number to view the article in the Microsoft Knowledge Base:

833797 How to back up and restore installations of Windows SharePoint Services that use Microsoft SQL Server 2000 Desktop Engine (Windows)

Keywords: kbhowto kbinfo KB843580

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.