Hi There! Today we are going to discuss How To Rename SQL Server Database the best way. Renaming a SQL Server database can be a straightforward process, but it is essential to follow the right steps to ensure data integrity and avoid disruptions. Trust me most of the DBAs think it is too easy to perform and end up missing an essential step which is discussed here.

Why Rename SQL Server Database?

Before jumping into the steps, it’s important to understand why you might need to rename a database. Common reasons are as follows:

  • Rebranding: Aligning the database name with a new organizational structure or branding.
  • Clarity: Improving the descriptiveness of the database name for better understanding among team members.
  • Organization: Consolidating database names for better management in multi-database environments.
  • Version Control: Adding version numbers or dates for better tracking.

Regardless of the reason, there may come a time when you want to or need to rename a database. In this tip we’ll look at the best option.

Prerequisites to Rename SQL Server Database:

First ensure the following:

  • Backups: Always have a recent backup of your database. This provides a safety net in case something goes wrong during the renaming process.
  • Permissions: Verify that you have the necessary permissions to rename the database. Typically, you need to be a member of the db_owner role or have ALTER permission
  • Downtime: The process needs downtime and hence always secure downtime approval/inform stake holders as per your organization process.
Two Different Methods can be used to Rename SQL Server Database:
  • Using SSMS (SQL Server Management Studio)
  • Using T-SQL

First of all let’s check and note the database details by running the following query:

USE SQLGeeksPro -- Change the db Name
GO
SELECT DB_NAME() AS DatabaseName, file_id, name AS logical_name, physical_name
FROM sys.database_files
GO

The Results will be as shown below:

Rename SQL Database

Make a note of the highlighted details in the above example.

Rename SQL Database using SSMS:
Step 1: Connect to SQL Server

Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance that contains the database you want to rename.

Step 2: Right click on the database and select the Rename option

Rename SQL Database

 

Step 3: Type in the new name you want for the database

Rename SQL Database

Rename SQL Database using T-SQL:
Step1: In SSMS open a new Query Window 
Step2: Execute the below Query:
USE master
GO
ALTER DATABAE [CurrentDatabaseName] MODIFY Name [NewDatabaseName] -- Change the CurrentDatabaseName and NewDatabaseName as required

For Example:
Rename SQL Database

This command works for SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022. Alternatively you can use sp_renamedb as well.

USE master
GO
EXEC sp_renamedb 'CurrentDatabaseName','NewDatabaseName'

Most of us think that in this way we have completed the database renaming. In other words you’ll be able to see the new name in SSMS. Important to realize and point often overlooked is we have not changed the logical and physical file name. To be sure execute the very first query provided above to check the file names:

Result will be as shown below:

Rename SQL Database

As can be seen in the highlighted portion in green, only the database name got changed and there is no change for logical and physical file names. This may cause confusion in future even if we keep the technical issues aside. We can discuss it in another blog post.

Therefore you need to change the logical and physical file names as well.