May 19, 2022

Rename .mdf and .ldf files


Try the below steps to rename the .mdf and .ldf files

1) Run the below command to get the logical file name's and physical file names of the DB.

USE AXDB

GO

SELECT file_id, name as [logical_file_name], physical_name

FROM sys.database_files

 Result : 



2) Disconnect the existing connecting and bring the DB to offline by running below scripts

USE [master];

GO

--Disconnect all existing session.

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

--Change database in to OFFLINE mode.

ALTER DATABASE AXDB SET OFFLINE

3)  Refresh the DB's and check the status of AXDB should be offline

4)  Go to file locations and change the files names

5) then run the below cmd

ALTER DATABASE AXDB MODIFY FILE (Name='AXDBUAT', FILENAME='G:\MSSQL_DATA\AXDB.mdf')

GO

ALTER DATABASE AXDB MODIFY FILE (Name='AXDBUAT_log', FILENAME='H:\MSSQL_LOGS\AXDB_log.ldf')

GO

6) To bring back the DB online run below cmd

USE [master];

GO

ALTER DATABASE AXDB SET ONLINE

Go

ALTER DATABASE AXDB SET MULTI_USER

Go

7) Run the below cmd to check and confirm the status of the DB 

Select name as [AXDB],state_desc from sys.databases 



8) ReRun the below cmd to see the changed names