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

Detach the Database:

use master go  sp_detach_db 'mydb' 

Move the Database files (Xcopy through xp_cmdshell shown):

DECLARE @SRCData nvarchar(1000) SET @SRCData = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb.mdf'; DECLARE @SRCLog nvarchar(1000) SET @SRCLog = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf'; DECLARE @FILEPATH nvarchar(1000); DECLARE @LOGPATH nvarchar(1000); SET @FILEPATH = N'xcopy /Y ' + @SRCData + N' D:\Data'; SET @LOGPATH = N'xcopy /Y ' + @SRCLog + N' E:\Log'; exec xp_cmdshell @FILEPATH; exec xp_cmdshell @LOGPATH; 

ReAttach Database:

sp_attach_db 'mydb', 'D:\Data\mydb.mdf', 'E:\Log\mydb_log.ldf' 

There's more detail at this Microsoft KB article.

Answer 2

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.