Move SQL Server Databases Using Detach and Attach

--How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
--Prerequisites
--Note You can determine the name and the current location of all files that a database uses by using the 

sp_helpfile stored procedure

use
go
sp_helpfile
go

--Detach the database as follows:

use master
go
sp_detach_db 'mydb'
go

--Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
--Re-attach the database. Point to the files in the new location as follows:

use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go

--Verify the change in file locations by using the sp_helpfile stored procedure:

use mydb
go
sp_helpfile
go

--The filename column values should reflect the new locations.