Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Rename logical database file name for a SQL Server database


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Administration

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical file names of your database files then this tip will be very useful. This way you can keep your logical database file names unique on your SQL Server instance.

Solution

It is not mandatory to keep logical file names unique on a SQL Server instance, but ideally we should keep them unique to avoid any confusion.

When we restore a database, the logical database file names don't change and are the same as the source database. So if you are restoring to multiple databases with the same backup file, the logical file names will be the same for all of the restored databases.

Let's start with creating a database having three data files and a log file then we will change the logical file name of these database files to show the step by step method.

Rename the logical SQL Server database file name using T-SQL

Step 1: First we will create a database named "Manvendra" with three data files (1 primary and 2 secondary data files) and one log file by running the below T-SQL code. You can change the name of the database, file path, file names, size and file growth according to your needs.

CREATE DATABASE [Manvendra]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Manvendra', FILENAME = N'C:\MSSQL\DATA\Manvendra.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_1', FILENAME = N'C:\MSSQL\DATA\Manvendra_1.ndf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_2', FILENAME = N'C:\MSSQL\DATA\Manvendra_2.ndf' ,SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
 LOG ON
( NAME = N'Manvendra_log', FILENAME = N'C:\MSSQL\DATA\Manvendra_log.ldf',SIZE = 10MB , MAXSIZE = 1GB , FILEGROWTH = 10%)
GO

Step 2: Next I created a backup of the database and then restored the database to "Manvendra_Test".

BACKUP DATABASE [Manvendra] TO DISK = 'C:\MSSQL\Backup\Manvendra.bak'
GO

RESTORE DATABASE [Manvendra_Test] FROM DISK = 'C:\MSSQL\Backup\Manvendra.bak'
GO

I ran the below T-SQL command to get the logical file names of each database file for both databases.

USE MANVENDRA
GO
SELECT file_id, name as [logical_file_name],physical_name
from sys.database_files

USE MANVENDRA_Test
GO
SELECT file_id, name as [logical_file_name],physical_name
from sys.database_files

We can see the logical file names are the same for both databases.

Check logical SQL Server file names

Step 3: Now we will change the logical file name for the primary data file for database Manvendra from "Manvendra" to Manvendra_Data" by running an ALTER DATABASE statement using MODIFY FILE. To modify the logical name of a data file or log file, we will specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. Run the below command to apply this change.

USE [master];
GO
ALTER DATABASE [Manvendra] MODIFY FILE ( NAME = Manvendra, NEWNAME = Manvendra_Data );
GO

Change logical file names of a SQL Server database file

Step 4: Now we will check the logical file names to verify our change we made in the last step. Run the command below to check the logical names.

USE [Manvendra];
GO
SELECT file_id, name AS logical_name, physical_name
FROM sys.database_files

We can see the logical file name has been changed without any downtime of the database.

Validate the logical SQL Server database file name changes

Step 5: If you have multiple database files for which you need to change the logical file names this can also be done using a T-SQL command. Let's change the logical file name of all database files of our database "Manvendra".

USE [master];
GO
ALTER DATABASE [Manvendra] MODIFY FILE ( NAME = Manvendra_Data, NEWNAME = Manvendra );
GO
ALTER DATABASE [Manvendra] MODIFY FILE ( NAME = Manvendra_1, NEWNAME = Manvendra_Data1 );
GO
ALTER DATABASE [Manvendra] MODIFY FILE ( NAME = Manvendra_2, NEWNAME = Manvendra_Data2 );
GO
ALTER DATABASE [Manvendra] MODIFY FILE ( NAME = Manvendra_Log, NEWNAME = Manvendra_Log1 );
GO

Logical file name change of all SQL Server database files

Now check the logical file names of all four database files again. You can see all logical file names have been changed and the logical file name of the primary data file has been changed back to "Manvendra".

Verify the logical file name change of all SQL Server database files

Rename logical database file names using SQL Server Management Studio

Step 1: Logical file names can also be changed using SQL Server Management Studio. Select the database in Object Explorer and right click on the database and select Properties. On the Files page, we can see the logical file names.

Logical database file names in SQL Server Management Studio

Step 2: Here we will rename the logical file names and append "_GUI" to each file name as shown in the below screenshot and click OK to save the changes.

Change the SQL Server logical file names using SQL Server Management Studio

Step 3: If you open the database Properties window again we can see the logical file names have been changed.

Verify the logical file name change of all SQL Server database files

We can also see that the logical file names have been changed by running the below T-SQL command again.

Verify the logical file name change of all database files with T-SQL
Next Steps
  • Do not change logical names of any data file of your production database until it is required or needed.
  • First test this process in lower life cycle environments and then replicate the change in production post approval process.
  • Explore more knowledge with these SQL Server Database Administration Tips.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, November 29, 2016 - 5:00:54 PM - Jessica Back To Top

 Hi Manvendra,

 

Is there a way to automate changing logical name for all databases on a given server? For instance changing logical name from "DatabaseName__PRIMARY__01" to "PRIMARY__01" for all databases at once?

 

Thank you.

 


Learn more about SQL Server tools