How to migrate an MSSQL Server Database from one location to another

A database in MSSQL server is stored in two files, namely .ldf file and a .mdf file. In case of MSSQL Server 2008 , these files are by default located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA directory.

In order to move these files from one MSSQL server instance to another MSSQL instance, we need to copy these file from one location and paste at another location. For that, first we should detach the db. It can be done from the management studio using the following commands.

Assuming that the database name is mydb following sql query will detach the db from the server. 

use master
go
alter database mydb set single_user with rollback immediate
alter database mydb set restricted_user with rollback immediate
go
exec sp_detach_db mydb
go

Now the data file and log file can be copied without issue to the destination. Now in order to re attach the database, use the following command.

EXEC sp_attach_db @dbname = N'mydb',     @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\mydb.mdf',     @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\mydb_log.ldf'