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)
5 Comments. Leave new
Thanks, helpfull!
Thanks, Dave. Relatively simple instructions for a relatively simple operation. I tried to follow three other articles before finding yours. All the other articles had much more noise. This solved my problem in about five minutes. I appreciate your effort to keep things simple.
Thanks Pinal…I always come here for strange syntax issues :) –Kevin3NF
This was a great help. Easiest and quickest solution online, thank you!
Thanks, it’s very useful !!!