Renaming Physical Database File Names for a SQL Server Database

By: Manvendra Singh   |   Updated: 2016-09-02   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | More > Database Administration

Problem

Sometimes we need to rename our databases on SQL Server instances. When we rename a database, the database name changes but the physical database file names remain the same, so it becomes confusing to identify the corresponding database files for that database. As a best practice we should rename the physical database files to match the database name in such cases to avoid confusion. If you want to change the physical database file names of your database files then this tip is very useful.

Solution

As we all know, SQL Server database files have two names, one is known as the logical file name and the other is the physical file name. The physical file name is the name of the actual file in the Windows operating system.

When we rename a database, the database name changes but the physical database file name remains same.  This can be confusing to identifying the corresponding database for the files. We should rename the database files to match the database name to avoid any confusion.

There are multiple ways to rename your physical database files of your SQL Server database. You can bring your database offline then you can execute ALTER statements after renaming the actual files or you can use the detach attach method to rename the files. In this tip, I will show you the step by step method to rename any physical database files by bringing the database OFFLINE and then running a few ALTER statements. I will explain the step by step method of the detach and attach method in my next tip.

NOTE: EXECUTE A FULL BACKUP OF THE DATABASE BEFORE MAKING ANY CHANGES. DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS.

Renaming Physical Database File Name for a SQL Server Database

Step 1: First we will create a database named "Manvendra" with three data files (1 primary and 2 secondary data files) in one filegroup and one log file by running the below T-SQL code. You can change the name of 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'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_1', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_1.ndf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_2', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_2.ndf' ,SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
 LOG ON
( NAME = N'Manvendra_log', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_log.ldf',SIZE = 10MB , MAXSIZE = 1GB , FILEGROWTH = 10%)
GO

Step 2: Now we will check the physical database file names by running the below T-SQL code to validate the database creation.

USE Manvendra
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files

You can check this information using SQL Server Management Studio by launching the database properties window as shown in the below screenshot.

We can see the physical and logical file names for the newly created database.

Our next step is to rename the physical database file names. The physical database file names cannot be changed or renamed while the corresponding database is ONLINE because these files will be in use by SQL Server, so we need to bring our database OFFLINE. If you try to rename these files while your database is online, you will get the below error.

Step 3: Since we cannot rename or change any physical file name while the database is online we will bring our database OFFLINE by running the below T-SQL statement. If there are any critical transactions running then it's recommended to let them complete first then go ahead with this step.

USE [master];
GO
--Disconnect all existing session.
ALTER DATABASE Manvendra SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Change database in to OFFLINE mode.
ALTER DATABASE Manvendra SET OFFLINE

Check and validate the database status before moving ahead with renaming of the physical files. You can either check the status by running the below T-SQL code or you can refresh your database folder in to SQL Server Management Studio. Once you do that you will see info like the image below.

Step 4: Since the database is OFFLINE we can rename the physical database file names of the target database.  The picture below shows we have renamed all associated database files and added the suffix "Renamed" to easily identify the files.

Step 5: Once the physical database files have been renamed, the next step is to update the system catalog where the older file name is present. Run the below ALTER statements for each file to be renamed separately. I am renaming all four files, so I will run four ALTER statements.

ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_Renamed.mdf')
GO
ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra_1', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_1_Renamed.ndf')
GO
ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra_2', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_2_Renamed.ndf')
GO
ALTER DATABASE Manvendra MODIFY FILE (Name='Manvendra_log', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_log_Renamed.ldf')
GO

Step 6: Once the system catalog is updated the next step is to bring the database ONLINE. Run the below T-SQL statement to bring the target database ONLINE. You can use SQL Server Management Studio as well to bring the database ONLINE.

ALTER DATABASE Manvendra SET ONLINE
Go
ALTER DATABASE Manvendra SET MULTI_USER
Go

Once the above command is executed successfully, the database will be online for normal operations. You can check whether the database is online or not by running the below T-SQL code or by refreshing the "Database" folder in SQL Server Management Studio.

SELECT name as [Database_Name], State_desc from sys.databases

Step 7: The final step of this tip is to validate the logical and physical file name changes which we worked on in this tip. We will run same command which we ran in step 2 to get the physical database file names of the target database.

USE Manvendra
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files

You can see the physical database file names have been changed for each file of this database. You can compare these names with the screen shot in step 2. We can also validate this change through Management Studio by launching the Database Property window of the database. Choose the "Files" tab on the left side pane of this window as shown in the below screenshot.

Next Steps

• Do not change physical database file names of your production database until it is required or needed.
• First test this process in to lower life cycle and then replicate the change in to production post approval process.
• After restoring databases with a different name, use this tip to change the physical file names.
• Explore more knowledge on SQL Server Database Administration Tips.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips