.::: Move SQL Server MDF and LDF Files location :::.

Step 1: Original Location

SELECT @@servername as ServerName,name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb');
 

Step 2: Take Database Offline
ALTER DATABASE secretdb SET OFFLINE;

 


Step 3: Move Files – MDF and LDF

open command prompt &

 
move c:\programfile\mssql\secretdb.mdf c:\data\secretdb.mdf ;
move c:\programfile\mssql\secretdb_log.ldf c:\data\secretdb_log.ldf ;

Step 4: Change the file location inside SQL Server
ALTER DATABASE secretdb
MODIFY FILE ( NAME = secretdb, FILENAME = 'c:\data\secretdb.mdf' );
ALTER DATABASE secretdb
MODIFY FILE ( NAME = secretdb_Log, FILENAME = 'c:\data\secretdb_log.ldf' );
GO

Step 5: Bring Database Online
ALTER DATABASE secretdb SET ONLINE;

Step 6: Check the database file location
SELECT @@servername as ServerName,name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb');