Datafile & log file Movement

Prerequisites:
1. Should take DB backup before performing files movement.
2. Drives where data & log files should have enough space.
3. Find out location of each files of database using  sp_helpfile   or  sys.database_files
4. Do not keep the Database files on the same location where OS is installed.
5. Schedule a downtime to move the database when no users are connected to the application.
6. Ensure you have admin access.

Approach 1:- 
Step 1:   Stop application. 

Step 2:  Stop SQL service. 

Step 3:  Detach the database as follows: 

                 use master 

       go 

       sp_detach_db 'userdatabase' 

       go 

Step 4: Next, copy the data files and the log files from the current location (D:\) to the new location(E:\). 

Step 5: Re-attach the database. Point to the files in the new location as follows: 

                use master 

       go 

    sp_attach_db 'userdatabase','E:\userdata.mdf','E:\userlog.ldf' 

       go 

Step 6:  Verify the change in file locations by using the sp_helpfile stored procedure: 

       use userdatabase 

       go 

       sp_helpfile 

       go 

 The filename column values should reflect the new locations. 

Approach 2:-

Step 1 : Get data file & log file name by issuing below command

       Use <DB_Name>

       sp_helpfile   or  select * from sys.database_files 

Step 2 : Take the database you want to work with offline by typing these commands:

       ALTER DATABASE Personnel SET offline GO

Step 3 : Move one file at a time to the new location by typing the following:

 ALTER DATABASE DBNAME MODIFY FILE ( NAME = Personnel_Data, FILENAME = "E:\Data.mdf") 

 ALTER DATABASE DBNAME MODIFY FILE ( NAME = Personnel_Log, FILENAME = "E:\log.mdf") 

Step 5 : Repeat the previous step to move other data and log files.

Step 6 : Put the database online by typing the following commands:

                        ALTER DATABASE Personnel SET online GO