SQL Server - Detach and Attach

As the name indicates, the detach and attach method for using database disconnect and connect on a sql server system.We can simply detach a database on sql server with ssms interface or using query.But while attaching database on server system,we need to give the mdf and ldf file path with query.Mainly using these method for moving file path of database from one location to other.

We can determine the name and the current location of all files that a database uses by using the
sp_helpfile stored procedure.

use master
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:\DataFile\) to the new location (E:\DataFile\).
Re-attach the database. Point to the files in the new location as follows:

use master
go
sp_attach_db 'mydb','E:\DataFile\mydb.mdf','E:\DataFile\mydb.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.