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.

SQL SERVER - Rename Logical Database File Name for Any Database logicalrename

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:

SQL SERVER - Rename Logical Database File Name for Any Database logicalrename1

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.

SQL SERVER - Rename Logical Database File Name for Any Database logicalrename2

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)

File format, SQL Scripts, SQL Server
Previous Post
SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String
Next Post
SQL SERVER – Rename Physical Database File Name for Any Database

Related Posts

2 Comments. Leave new

  • Good job

    How to change the physical name ?

    Reply
  • There are cases when the NAME value will need to be enclosed in quotes. For example, if the original logical name was SQLAuthority.mdf, the ALTER DATABASE line will look like this:

    ALTER DATABASE [SQLAuthority] MODIFY FILE ( NAME = ‘SQLAuthority.mdf’, NEWNAME = SQLAuthority_Data);

    Reply

Leave a Reply