Matt Johnson's Technical Adventures

Moving WSS 3.0 Databases

While building a Windows SharePoint Services 3.0 server, you can't specify the location of the Microsoft SQL Embedded Edition (MSEE) databases. As any good admin would do, I wanted to separate my data from my system files. So I set out to move the databases that SharePoint uses. Here is the step by step I used to move them.

1. Download and install the Microsoft SQL Server Native Client and Microsoft SQL Server 2005 Command Line Query Utility from here.
   
2. Open up services.msc and stop the following services:
• Windows SharePoint Services Administration
• Windows SharePoint Services Search
• Windows SharePoint Services Timer
• Windows SharePoint Services Tracing
• Windows SharePoint Services VSS Writer
3. Open a command prompt and go to the following location:
• C:\Program Files\Microsoft SQL Server\90\Tools\binn
4. Enter the following command
• sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E
5. To get the list of your SharePoint databases enter the following commands
• select name from sys.databases
• go
6. You can now move any of the databases that start with SharePoint_ or with WSS_. You start moving the databases by typing the following command to detach the database:
• exec sp_detach_db "<sharepoint_database_name>"
• go
  
7. Open up Windows Explorer and browse to the following location:
• %SystemRoot%\sysmsi\ssee\mssql.2005\mssql\Data
8. Copy both the .mdf and .ldf files for the database you just detached from SQL to your desired location.
9. Now attach the database by entering the following command at the command prompt.
• exec sp_attach_db @dbname = N'<Database_Name>', @filename1 = N'<New_Location>\<Database_Name>.mdf', @filename2 = N'<New_Location>\<Database_Name>_Log.ldf'
• go
10. Repeat steps 6 through 9 for each database you want to move.
11. Type Exit to close the SQL Command Line Utility.
12. Start the following services:
• Windows SharePoint Services Administration
• Windows SharePoint Services Search
• Windows SharePoint Services Timer
• Windows SharePoint Services Tracing
• Windows SharePoint Services VSS Writer
13. Verify you can now access your SharePoint sites.