What is the proper way to move a database from one drive to another in SQL Server 2005?

Question

I'm not looking to relocate the database to another server entirely, but just move the data file(s) and log file to another drive with more space. I've seen conflicting directions on how to do this, so I'm looking for the recommended proper way of doing it.

Answer 1

Did you take a look at this?
http://support.microsoft.com/kb/224071

Answer 2

To be absolutely safe, I would do the following:

1. Backup the database to a BAK file.
2. Take the current database offline, or delete it if you want to.
3. Restore the database and change the location of the MDF and LDF files.

Scripts sample:

-- Get the file list from a backup file.  
-- This will show you current logical names and paths in the BAK file
RESTORE FILELISTONLY FROM disk = N'C:\Backups\MyDatabaseName.bak'

-- Perform the restore of the database from the backup file.  
-- Replace 'move' names (MDFLogicalName, LDFLogicalName) with those found in 
-- the previous filelistonly command
restore database MyDatabaseName
from disk = N'C:\Backups\MyDatabaseName.bak'
with move 'MDFLogicalName' to 'D:\SQLData\MyDatabaseName.mdf',
     move 'LDFLogicalName' to 'D:\SQLLogs\MyDatabaseName_log.ldf',
replace, stats=10;

Notes

The first script will get you the current names and paths that you'll need in the second script. The second script restores the database back to the name you want it to have, but you can change where you want to store. In the example above, it moves the MDF and LDF files to the D: drive.

Answer 3

Another way - detach database files (database->tasks->detach), move them to new drive and then attach again. But way described by Jay S is the simpliest.