Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Renaming Physical Database File Names for a SQL Server Database


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


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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

Check physical file names for the SQL Server Database

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

Check physical file names in SQL Server Management Studio

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.

Error during Changing Physical file names due to file in use

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

Switch SQL Server database to offline mode

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.

Check the SQL Server database to see if it is in offline mode

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.

Rename the database files in Windows

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

Run SQL Server T-SQL ALTER statements to update system catalog

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

Bring the SQL Server database Online

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

Verify the SQL Server database is online

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

Verify the SQL Server database file name changes

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.

Verify the SQL Server database change in SSMS
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.


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, August 22, 2017 - 11:39:02 AM - David Derocher Back To Top

 Hello -

 

I am renaming the physical SQL database files using the SQL Server Mgtmnt Studio GUI.

 

In SSMS 2008R2 I follow these steps:

1. Detach the DB

2. Rename the .mdf and .ldf files in windows

3. Re-attach the DB

4. Browse to the new .mdf and .ldf during the reattach since SSMS cannot find the correct files

This works great

 

If I repeat the steps using SSMS 2016 I am not able to browse to the new .mdf and .ldf as described in step 4.  This does not show up as it did in 2008 R2.

 

Is there a setting somewhere that i am missing?

 

Thanks for any help,

 

David

 

 


Monday, September 05, 2016 - 10:23:09 AM - Vishal Back To Top

 Is it possible to change the database name without changing its Physical file names???

 


Learn more about SQL Server tools