Methods To Rename SQL Server Database

Stephen West | December 17th, 2015 | SQL Transaction Log |

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

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

select single user

From Open Connections dialog box, click on Yes

rename sql server database

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

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 command line

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

detach sql database using t-sql

attach sql database using t-sql

Using SQL Server Management Studio

Detaching SQL Server Database using SQL Server Management Studio

detach database

Attaching and renaming SQL Server database using SQL Server Management Studio

attaching and renaming database

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.

rename physical file

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.

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.