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

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.

(http://msdn.microsoft.com/en-us/office/bb421687.aspx)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: