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)
2 Comments. Leave new
Good job
How to change the physical name ?
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);