The SharePoint Swiss Army Knife

There’s been quite a few requests on message boards for instructions on how to move the default Windows Sharepoint Services ver. 3 databases and I got tired of looking up Ian Morrish’ blog so I thought I’d do a post here.

When you install WSSv3 and use the default installation method for a stand-alone server, it will also install the 2005 version of the Microsoft SQL Server Desktop Engine. That’s the free internal database so you wouldn’t have to install SQL Server although there are advantages to having SQL Server, but that’s another topic. Unfortunately the install never gives you the option of storing your database anywhere else other than on your C:\ drive.

So, in order to change this according to WSSDemo.com:

“When referring to the database server name in any stsadm commands, you should use this name:

ServerName\Microsoft##SSEE

It has many advantages over the previous WMSDE that shipped with WSS 2.0 but there are 2 downsides:

1. no database admin tools are install. The only options are the sqlcmd command line utility (replaces the old osql utility) or Management Studio Express (both must be run on the server as no remote connections to WID are allowed).
2. you can’t specify the install location of WID when you install WSS

If you don’t have another SQL Server 2005 product installed on the WSS server then you will have to download sqlcmd from here… (also install the Native Client from the same link). After installation, you will find the utility installed in the following location:

C:\Program Files\Microsoft SQL Server\90\Tools\binn

Run the following command from this directory to connect to the WID instance:

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E

All the SharePoint database files will default to being in the C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data directory. To move them to another partition you should use the WSS Central Admin to mark the content database off-line (and note the content db name, then use the following sqlcmd commands…

EXEC sp_detach_db @dbname = ‘Content_Database_name’
Go

You will find the db and log files in the following location

WINDIR%\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\<dbname>.mdf’ and <dbname>_log.ldf

Now copy the files to the new location and run the following command

EXEC sp_attach_db @dbname = ‘Content_Database_name’, @filename1 = ‘drive:\path\Data\<dbname>.mdf’, @filename2 = drive:\path\Data \<dbname>_log.ldf’
Go

Now you can use the central admin site to mark the content db on-line.

There are 3 other SharePoint databases you may want to move to another location. The largest of these will be for search. To list them, use the following sqlcmd command:

select name from sysdatabases
go

Which should give you something like this (GUID’s may be different):

SharePoint_Config_c464b7ce-59ef-4820-9f75-f46a0937c08e
SharePoint_AdminContent_451452bf-9dc0-40c9-be18-14f14bc23007
WSS_Search_NETSERVER_86a140c5958d4a5d97c8c2cbee745424

Before trying to move these you should stop IIS and all the Windows SharePoint Services. Then you can use the same steps as above. “