Detach and Attach MSSQL DB

-- Get the datafiles location for the DB which you want to move
use MY_DATABASE
go
sp_helpfile
go

-- Deatch the DB which you want to move
use master
go
sp_detach_db 'MY_DATABASE'
go

-- Copy the files listed from step1 to new destination directory

-- Attach the DB with new files location
use master
go
sp_attach_db 'MY_DATABASE','D:\MSSQL\DATA\MY_DATABASE.mdf','D:\MSSQL\DATA\MY_DATABASE_1.ndf','D:\MSSQL\DATA\MY_DATABASE_2.ldf'
go


-- Get the New datafiles location for the DB which you moved
use MY_DATABASE
go
sp_helpfile
go


--For More Help, use below link
--http://support.microsoft.com/kb/224071