But I don't want the content database in the C:\ drive...

I’m don't know about you, but I’m not very SQL Server savvy.  I work with SharePoint mainly as an end user, and whatever SQL Server does in the background, I could care less as long as I see my sites.  But at times, you have to give in and put that SQL Server Admin hat on.  This is one of those instances.

As my SharePoint sites grew and various Web applications were added, I started to notice my C:\ drive was shrinking, and fast!  I soon discovered that the databases’ Data and Transaction log files were all stored on C:\!  Uh oh, this can turn really bad fast, but how do I move those files to a different drive AND configure SharePoint to do the same for all future Web applications?

I searched and searched the trusty SharePoint administrator’s SharePoint Central Administration for an answer but to no success.  After doing a little research online, I came to realize that the SQL Database location is controlled by SQL Server directly and does not involve SharePoint at all.

Here are the steps of how to move existing databases from C:\ to a different drive.  A heads up, these steps are defined for SQL Server 2005 and WSS v3 or MOSS 2007.

First you have to stop the running SharePoint Services.  If you are running a SharePoint farm, this means you have to go to each of your web front end (WFE) servers and stop the SharePoint services (they all start with the label Windows SharePoint Services). 

Next is to detach the databases from SQL Server 2005.  There are two methods for achieving this; through the SQL Server 2005 user interface, or through SQL commands using SQL Query (preferred).  Below we will walk through both methods:

Detaching Databases Using SQL Server 2005 Interface

1. Open SQL Server Management Studio and connect to your SQL Server in Database Engine mode.
2. Navigate through the browser tree to the appropriate database where your content resides.
3. Right click on the database, open the menu Tasks and select Detach…
   
   
4. A window will appear with the database listed.  Confirm that the options Drop Connections and Keep Full Catalogs are selected and click OK.
   
   
   5. The database is now detached.

Detaching Databases Using SQL Query

1. Open SQL Server Management Studio and connect to your SQL Server in Database Engine mode.
2. Click on the button New Query.  A new tab will appear in your activity window.
3. Enter the following statement
   

   use master
   go
   sp_detach_db 'ContentDBName'
   go

   Where ContentDBName is the name of the database you are detaching.

4. Execute the updated statement.  If you receive “Command(s) completed successfully.” message, the database has been successfully detached.

Moving the database data and log files
Now that you have successfully detached the databases, we need to move the physical files to their new home.  Commonly these files are located in C:\Program Files\Microsoft SQL Server\MSSQL\Data\ directory.  Locate the appropriate files; this would include an .mdf (the data file) and a .ldf (transaction logs) and move them to their new home.  You can optionally store them in separate folders if you choose to do so.

Re-Attaching the databases
Now that the files have been moved, we will now re-attach the databases.  This again can be performed two different ways; either through SQL Server 2005 user interface or SQL Query (preferred), I will explain both below.

Attaching Databases using SQL Server 2005 Interface

1. Open SQL Server Management Studio and connect to your SQL Server in Database Engine mode.
2. Right click on the folder Databases and select Attach.
   
   
3. In the Attach Databases window, click on the button Add.  This will open the Locate Databases window.
   
   
4. Select the .mdf file of the recently moved database and select OK.
5. Notice 2 entries are displayed in Database Details section of the Attach Databases window.  Also notice that the entry for the Log file type has a message of Not Found.  This is because it is still looking in the original directory.  Click on the browse button in the Current File Path for the log file and select the appropriate file location in the window.
   
   
6. Repeat steps 2 through 5 for each additional database to attach.
7. Click OK when complete.  Databases are now attached.

Attaching Databases using SQL Query

1. Open SQL Server Management Studio and connect to your SQL Server in Database Engine mode.
2. Click on the button New Query.  A new tab will appear in your activity window.
3. Enter the following statement
   
   

   use master
   go
   sp_attach_db 'ContentDBName','LocalPathToFile\ContentDBName.mdf', 'LocalPathToFile\ContentDBName.ldf'
   go

   Where ContentDBName is the name of the database.  
   LocalPathToFile\ContentDBName.mdf is the new path location of the data file .mdf and the file name. 
   LocalPathToFile\ContentDBName.ldf is the new path location of the transaction log file .ldf and the file name. 

4. Execute the updated statement.  If you receive “Command(s) completed successfully.” message, the database has been successfully attached.

Restart the Windows SharePoint services on all WFE servers.  You have now successfully moved your database files to a new location.

How to set SQL Server 2005 to create all future databases in a different directory.
The following describes how you can setup SQL Server 2005 to create the database files in a predefined directory for all new Web Applications that are created.

1. Open SQL Server Management Studio and connect to your SQL Server in Database Engine mode.
2. Right click on the SQL Server node and select Properties.
   
   
3. In the Server Properties window, select the node Database Settings. 
   
   
4. In the Database Settings screen modify the default location for the database Data and Log file by clicking on the browse button next to the appropriate field in the Database default locations section.
   
   
5. Click OK.  All future databases created will be placed in the new specified directory.