Methods To Rename SQL Server Database

Stephen West ~ Published: December 17th, 2015 ~ SQL Transaction Log ~ 3 Minutes Reading

Introduction

Sometimes it happens that we need to rename SQL Server database because the database might have been restored using different names or the original name of the database is based on the some project that is no longer relevant to the database. Whatever the reason behind the scenes, this can be easily done.

In this article we look how to rename SQL Server database using different options.

Before Renaming SQL Server Database

ALTER permission is required on the database to rename
File group name (.ndf .mdf) are not changed, when renamed the database
You cannot rename the System database

Make sure that no one is using database if someone is using the database, then close all the existing connections and set the database to SINGLE USER mode.

To change the restrict access to SINGLE USER connect to SQL Server Instance. Right click on the database and from the drop down menu select Properties

From the Database Properties window, under Select a page pane, click on Option and select Restrict Action option as SINGLE_USER then click Ok

From Open Connections dialog box, click on Yes

Rename SQL Server Database Using SQL Server Management Studio

If you are running SQL Server Management Studio, then right click on the database which you want to rename and from the drop down menu select Rename

Enter the new database name (desired database name) and to see the changes refresh the database.

Rename SQL Server Database Using Transact-SQL

Use the below Transact-SQL script to rename SQL Server Database

Rename SQL Server Database Using Detach And Attach

You can also use detach and attach feature to rename SQL Server Database. For this first detach the SQL Server database and give the new database name while attaching the database. This can be done via SQL Server Management Studio and Transact-SQL:

Using Transact-SQL

Use the following T-SQL command to rename SQL Server database

Using SQL Server Management Studio

Detaching SQL Server Database using SQL Server Management Studio

Attaching and renaming SQL Server database using SQL Server Management Studio

Note: The techniques mention above allow you to rename the database but the physical file still have the same name.

Rename Physical Files

When SQL Server is detached then SQL Server releases the lock on the physical files and you can rename the physical files.

Conclusion

This article describes how to rename SQL Server database and physical file using SQL Server Management Studio, Transact-SQL and via database detach and attach method.