Changing the location of Microsoft SQL Embedded Edition (MSEE) files

Windows SharePoint Services v3 uses Microsoft SQL Embedded Edition (MSEE) for its data store. When MSEE is installed, the data files are installed to your C: drive by default. Well, like any good admin - we don't want data (that can grow exponentially) living on our system partition. However, you can't successfully move the data files for an MSEE instance using your normal SQL tools (most notably SQL 2005 Management Studio). Yep, you've got to resort to the command line . . .

First, you will need to have the Microsoft SQL Server Native Client and Microsoft SQL Server 2005 Command Line Query Utility installed.
1) Identify the Sharepoint DB you want to move (look under SystemRoot%\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data)

2) Stop SharePoint services.

3) Open a command prompt

4) Go to the Microsoft SQL Server 2005 Command Line Query Utility folder (under C:\Program Files\Microsoft SQL Server\90\Tools\binn)

5) Enter the following command & hit enter:
sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E

6) Enter the following commands & hit Enter after each:
EXEC sp_detach_db GO

7) Repeat step 6 for each database you want to move.

8) Move the individual .mdf & ldf files for the detached databases to the new location.

9) Attach moved databases. Return to your command prompt and enter the following command then press Enter:

EXEC sp_attach_db @dbname = N'',
@filename1 = N'\.mdf',
@filename2 = N'\.ldf'

The line breaks above are for ease of reading. When entering the command, don't use line breaks, just the the lines wrap. E.g.:

EXEC sp_attach_db @dbname = N'WSS_Content',
@filename1 = N'D:\SharePointDB\WSS_Content.mdf',
@filename2 = N'D:\SharePointDB\WSS_Content_Log.ldf'

10) Type GO and press Enter.

11) Repeat steps 9 & 10 for each database you moved.

12) Type Exit to exit from SQLCMD

13) Type Exit to close Command Prompt

14) Start SharePoint services you stopped previously.

15) Verify access to SharePoint sites.