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. It becomes confusing to identify the corresponding database files for that database. As a best practice, we should rename the database files to match the database name 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 you may or may not know, SQL Server database files have two names: the logical file name and the physical file name. The logical name is what SQL Server sees internally and 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 identify the corresponding database with its files. We could rename the database files to match the database name to avoid confusion.
There are multiple ways to rename physical database files of your SQL Server database. You can bring your database offline and 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.
NOTE: EXECUTE A FULL BACKUP OF THE DATABASE BEFORE MAKING ANY CHANGES. DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER-LIFE CYCLE ENVIRNOMENTS.
Renaming Physical Database File Name for a SQL Server Database
Step 1: Create a Database
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 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'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%)
GOStep 2: Validate Database Creation
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: Take Database Offline
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, 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 the physical files. You can either check the status by running the below T-SQL code or you can refresh your database folder in SQL Server Management Studio. Once you do that, you will see info like the image below.
Step 4: Rename File Names
Since the database is OFFLINE, we can rename the physical database file names of the target database. The picture below shows that we have renamed all associated database files and added “Renamed” to easily identify the files.
Step 5: Update System Catalog
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: Bring Database Back Online
Once the system catalog is updated, it is time 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: Validate Changes
The final step is to validate the logical and physical file name changes. We will run the same command from 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 changed for each file of this database. You can compare these names with the screenshot 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.
