SQL SERVER – Rename Logical Database File Name for Any Database

During the recent Comprehensive Database Performance Health Check, we had to restore a database on a server. When we restored a database, we realized that the logical file name of the database was incorrect and we had to change that to something more consistent with the organization’s naming convention. Let us see how we changed logical database file name in a few quick steps.

What is Logical Database File 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_name, physical_name
FROM sys.database_files
GO

When you run above command you will see the following results:

When you want to do any T-SQL related operation, you will have to use the logical name in your SQL Server commands. Some people like to keep the logical filename unique across their SQL Server instance but that is not a requirement from SQL Server

Change the Logical Database File Name

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

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

USE [master];
GO
ALTER DATABASE [SQLAuthority] MODIFY FILE ( NAME = SQLAuthority, NEWNAME = SQLAuthority_Data);
GO
ALTER DATABASE [SQLAuthority] MODIFY FILE ( NAME = SQLAuthority_log, NEWNAME = SQLAuthority_Logs);
GO

Once you run the above script, you can once again run the command to check the logical filename and you will find the name of the file is not changed.

You do not have to restart your server to make the change effective.

Well, that’s it, changing the logical database file name is very easy.

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