ProblemSometimes we need to rename our databases

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.

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

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.

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.

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.

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.

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.

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

Last Update:

2016-09-02

About the author