Data File Movement in Sql Server

*Purpose of file Movement

You may need to move the DB file’s on other drive location due to following reasons.

1] In case of drive storage is full and you are not able to extend the drive storage.
2] The R/W speed of drive is very low and facing high disk I/O for the drive.
3] Log file and data files are in same location, As per best practices which should not be on same drive.

*Pre-requisites

In case a database is being used by any Windows services or other resources, these must be stopped in order to allow altering SQL database files.
Also, any existing connections to a database must be closed. Before the first step, 
make sure to locate the appropriate MDF and LDF files for a database you want to work with. By default, these names are in the following format:
Database_name_Data.mdf – for MDF file
Database_name_log.ldf – for LDF file
The above mentioned format does not need to be necessarily used, so make sure you are targeting correct files.
run 'Sp_helpfile' on DB to check the file location
Take database full backup for safe side. (if possible)

*Moving database files to another location 

Run the following SQL script to set a new location for SQL database files:

Use [Master]
Go
ALTER DATABASE AdventureWorks2014   
    MODIFY FILE ( NAME = AdventureWorks2014_Data,   
                  FILENAME = 'E:New_locationAdventureWorks2014_Data.mdf');  
GO

 *Run the following SQL script to take a SQL database offline:

Use [Master]
Go
ALTER DATABASE AdventureWorks2014 SET OFFLINE;  
GO

This is important in order to perform the next step. If a database is being used by any application, this step cannot be accomplished, 
unless all connections to a database are closed.

* Move the physical files to new location

Move MDF /LDF files of the specific SQL database to a new location specified in the statement above. 
This means to simply cut mentioned files from the existing location and to move them to a newly specified one.

Important note: Make sure that SQL Server can access the specified location. Otherwise, access denied error will occur when you start the DB services.
Also if the file size is large (in TB) use copy paste method and rename the old file once file is copied on new location to avoid any corruption during copy-paste.

*Run the following SQL script to take a SQL database Online

Use [Master]
Go
ALTER DATABASE AdventureWorks2014 SET OFFLINE;  
GO

*POA

1] Take DB Backup and note down the DB files location.
2] Run file movement command on DB
3] Make the DB offline
4] Move the physical file into new path
5] Make the DB online