Steps to Rename Physical Database Files in SQL Server

The users can manually rename the physical files according to choice using the Transact-SQL commands in the following manner:

Firstly, we will create a database “new_db” using SQL query. Naturally, the physical files will be named as “new_db.mdf”. Our main aim is to rename SQL Server physical files from “new_db.mdf” to “old_db.mdf”. Follow the below mentioned steps to manually rename the files:

1. Firstly, run the following command to create a new database with name ”new_db”.

CREATE DATABASE [new_db]
CONTAINMENT = NONE
ON PRIMARY
( NAME = 'name', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\new_db.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )

Thus, a new database is created with physical file “new_db”. The following steps can be used to rename SQL Server physical files name.

2. Now, locate the physical location of file on your system using the following command:

USE new_db
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files


3. Also, the database should be brought to OFFLINE state because the files cannot be renamed in the online mode. Run the following command to bring database to offline state:

USE [master];
GO
--Disconnect all existing session.
ALTER DATABASE new_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Change database in to OFFLINE mode.
ALTER DATABASE new_db SET OFFLINE


4. Since the database is offline now, so browse to the location of the file as found from Step(1). Rename the file to desired name e.g., old_db.mdf.
5. Now, update the system catalog for updating the new name of the physical file.

ALTER DATABASE new_db MODIFY FILE (Name='new_db', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\old_db.mdf') GO

This updates the physical file name in system catalog file.


6. Run the following command to bring the database in ONLINE mode:

ALTER DATABASE new_db SET ONLINE
Go
ALTER DATABASE new_db SET MULTI_USER
Go



So, the database physical file name has been renamed to “old_db.mdf” and the database is also in the Online mode. The users can now work normally on the database to perform any operation.

Conclusion

The user may feel it necessary to rename physical file name in SQL Server due to any reason. However, to avoid confusion between the physical files and database, the users are always advised to rename SQL Server physical database files using T-SQL. All the detailed steps have been mentioned in the above section.