Problem Description:

Many a times due to shortage of space on drive, application team decides to move data files from one location to another location on same SQL server instance. In this example, we are going to move data/log files from C: drive to D: drive

Solution:

Step1: First ensure that nothing is being run on that specific database before we start

sp_who2 active
go

Step2: Identify current location of data files

use Dbalyfe
go
select name, physical_name, state_desc as Status from sys.database_files
go

once above query is run, we will get its logical name and physical location details and will pass its logical name in below query to change its physical location to D: drive

Step3: Modify physical location of data/log files

alter database  Dbalyfe
modify file  (name =  Dbalyfe , filename = ' D:\MSSQL\MSSQLSERVER\Dbalyfe.mdf');

alter database Dbalyfe
modify file (name = Dbalyfe_log, filename = 'D:\MSSQL\MSSQLSERVER\Dbalyfe_log.ldf');

Step4: Offline the database

alter database   Dbalyfe  set offline

Step5: physical move of data file

physically move data file from existing location to new location by cut and paste.

Step6: Online the database

alter database   Dbalyfe  set online

Step7: Verify if physical file location is changed correctly

use <dbname>
go
select name, physical_name, state_desc as Status from sys.database_files
go