How to Move User Database .mdf and .ldf Files to Another Location

Let us Consider we have two folders location1 and location2. We want to move database files from loc1ation1 to location2. 

USE MASTER;

GO

ALTER DATABASE Production

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

Detach DB

EXEC MASTER.dbo.sp_detach_db @dbname = N'Production'

GO

Now move the files from location1 to location2. You can now reattach the files with new locations.

-- Move MDF File from Location1 to Location2

-- Re-Attached DB

CREATE DATABASE [Production] ON

( FILENAME = N'F:location2Production.mdf' ),

( FILENAME = N'F:location2Production_log.ldf' )

FOR ATTACH

GO