SQL Server 2Kx: How to move a database to a different partition in the local file system?

SQL Server creates its databases in the system program files directory structure.

I want to separate the database files away from the system partition.

Assuming the database is already created and populated: How do I move all files related to a database to a different location on a separate partition?

Assuming it's a user database, detach the database, move the .mdf / .ldf files to the new location and reattach the database. You might need to browse to the new location of the .ldf file when you choose your .mdf file in the reattach screen.

To detach a database, right-click on it in management studio and choose Tasks | Detach.

To reattach, right-click on Databases in management studio and choose Attach..., then browse to the new location of your .mdf and .ldf files.

For system databases it's a little more involved, especially the master db. Have a look at this MSDN article that gives you the steps. Pay attention to the info regarding the resources system db for SQL2005+.

Checkout KB 224071 which explains how to move user and system databases - How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Hope this helps!

Just to add a special note for SQL Server 2008. According to Microsoft, you can move database files without having to detach the database.

http://msdn.microsoft.com/en-us/library/ms345483.aspx

It basically amounts to taking the database offline and then executing ALTER statements that modify the filename mappings to the database/log files.