How to attach and Detach the Database in SQL Server ?

The Master data file (Mdf) and Log Transaction File (Ldf) of a database can be attached and detached to the same or different SQL server instances.

It is very helpful in moving the database files from one location to another location.

Example

In the following example, I am going to move the database file of studentdetail from one folder to another folder.

Initially the log files are located in E:/Data path.

Right click the database which you want to detach and select Tasks --->  Detach option as shown below.

Select the drop connection checkbox in the detach database wizard.

After Detaching the database , you can relocate the database files from E:/Data to E:/StudentData location as shown below.

Now we have replaced the files to another folder.

After relocating the files we need to attach the database using attach option as shown below.

Click Add button as shown below.

Select the Mdf file from the located path as shown below. 

Now we can see the database files path has been changed in the attach database wizard.

After clicking OK button you can see the attached StudentDetail database as shown below.

Now we have successfully detached and attached the database.