How to move a WSS 3.0 Sharepoint Database from one location to another
Like it or not, if you install Windows Sharepoint Services the databases will end up on the same drive as your install by default. Sometimes you may need to move the databases that Sharepoint uses. For example, I needed to move a database off of the small root drive (C:) of my Windows Server 2008 R2 installation onto another volume, labeled DATA E:. In fact, I consider keeping your databases separate from your install to be a best practice. Ideally, you’ll want databases to be stored on a database server so your DBM can give the DB’s the full attention they deserve. For this tutorial, we’ll show you how to move them to a new location local to the server. This can be accomplished with a few commands, but first you’ll need to install some things that aren’t readily available with a fresh Sharepoint installation.
Before you begin the tutoral
- Install Microsoft SQL Server Native Client.
- Install Microsoft SQL Server Command Line Utility.
- Do not reboot after installs. Well, you could, but why? It doesn’t ask you to.
Step 1: Figure out the path to the databases you want to move
By default, mine were all located here:
C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\
The main databases you’ll want to move will be the content database, named WSS_Content.mdf by default and the config db, named Sharepoint_Config_%randomnumbers%.mdf. In my experience, the search database grew far larger than I expected so I moved it as well. You may want to add it to your list of dbs to move. Best practice would, naturally, be to move all associated databases. This will keep things organized.
Step 2: Stop all Sharepoint Services that are currently running
Keep a note of the ones you stopped so you can turn them on later.
Step 3: Head to the CLQU in a command prompt
By default it is located under the Binn directory at C:\Program Files\Microsoft SQL Server\%number%\Tools\Binn. This was the third item I installed here, so my %number% was \100\. Some install CLQU before the Native Client, which puts it at \90\.
Step 4: Query the databases
You can copy and paste into your cmd prompt:
sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E
Step 5: Detach all the databases you want
If you entered it correctly, you’ll be given a 1. At the 1 line, enter the following command (the database name does not need the brackets or the .mdf extension):
EXEC sp_detach_db [your_database_name]
At the 2 line, enter the command:
GO
If you entered it correctly, you’ll be given another 1. See screenshot below:
Keep repeating this step for as many databases as you want.
Step 6: Move your databases
When you’ve detached all of your databases, you can then move all of .mdf files. You’ll also need to move the .ldf files that are in the same directory as them. We’ll be using the .ldf files to attach the db’s back to Sharepoint.
Step 7: Attach the moved databases
Now that the databases are moved, you can attach them again. You’ll need to know the name of the db, the location of the .mdf, and the location of the .ldf for this long command. We’ll use WSS_Content as the database example. Enter the extremely long command without line breaks:
EXEC sp_attach_db @dbname = N'WSS_Content', @filename1 = N'D:\SharePointDB\WSS_Content.mdf', @filename2 = N'D:\SharePointDB\WSS_Content_Log.ldf'
And at the 2 line enter:
GO
Step 8: Exit command prompt and restart services
Just like in step 5, if you did this correctly you’ll get another prompt. Repeat step 8 to attach all of the moved databases. Exit out of the SQLCMD by typing exit and close the command prompt by typing exit again or simply closing the window.
Start up all the noted Sharepoint Services you made before and check to make sure Sharepoint is working properly.
If you run into any specific errors or see anything wrong please let me know in the comments. I designed this tutorial for Windows heavy Network Admins that don’t really get their hands dirty with command line stuff all too often. Cheers!