SQL SERVER – Rename Physical Database File Name for Any Database

During the recent Comprehensive Database Performance Health Check, I was asked is there any way we can rename the logical database file name. I have blogged about it here: SQL SERVER – Rename Logical Database File Name for Any Database. Right after the blog post, I received another question where the user wanted to know if there is an easy way to rename the physical file. In this blog post, we will learn about how to rename the physical database files name.

SQL Server database files have two different names – 1) logical file name and 2) physical file name. You can see both the names of the files for your database when you run the following command.

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

Change the Physical Database File Name

Now let us see a simple script which can change the physical file name.

Please make sure that you select the appropriate database context for your script when you want to change the physical name.

If we want to rename any physical file, it is not possible to rename the file while the file is in the use. For the same reason, first, we will have to take the database offline.

If you have previously ever taken any database offline, you might be familiar with the situation, where we are not able to take that offline due to open connection to the databases.

To take the database successfully offline, first we will move the database to single user mode and right after that, we will take the database offline.

USE [master];
GO
ALTER DATABASE SQLAuthority
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SQLAuthority SET OFFLINE
GO

Now we will have to go to your file system and rename to the new name. In our case, I am changing the name of the log file.

Now we will have to run following alter database script where we will specify the physical file with its logical name as well as physical new name.

ALTER DATABASE SQLAuthority
MODIFY FILE (Name='SQLAuthority_Logs', FILENAME='D:\data\SQLAuthority_Logs.ldf')
GO

Once you see the above script running successful, we can run the following command to bring back the database online.

USE [master];
GO
ALTER DATABASE SQLAuthority SET ONLINE
Go
ALTER DATABASE SQLAuthority SET MULTI_USER
GO

Now once the database is online, you can once again check the logical and physical file name running following script.

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

Well, honestly it is that simple to rename the physical file.

Reference: Pinal Dave (https://blog.SQLAuthority.com)