How to Rename a SQL Server Database

When working with SQL databases for quite a long period of time, you might come across a situation when you need to give your database a different name. It can be done in several different ways. In this article, we will be talking about how to rename a database using the command line, SSMS, and dbForge Studio for SQL Server. Below, you will find a detailed illustrated guide applicable to SQL Server 2005-2022.

Contents

• Rename a SQL Server database using T-SQL
• Rename a SQL Server database using detach and attach
  • Detach and reattach using a T-SQL query
  • Detach and reattach in dbForge Studio for SQL Server
• Rename a SQL Server database using SSMS
• Conclusion

Before we start, it is important to mention that you will need to have exclusive access to the database you are going to rename. This means that you will need to make sure there are no other database connections using the database in question.

Rename a SQL Server database using T-SQL

First, we are going to talk about how to change the name of a database using a T-SQL query. Usually, it will look somewhat about:

ALTER DATABASE [sourcename] MODIFY NAME = [targetname];

Let’s say we have been running a bicycle store for quite a long time and we have used the same BicycleStore2007 database ever since the beginning of our business. Now, as time has passed, we wish to remove the year from its name. To do that, we will use the following query:

ALTER DATABASE BicycleStore2007 MODIFY NAME = BicycleStore; 

As a result, we see that there is no year in the database name anymore and it is simply BicycleStore. 

dbForge Studio for SQL Server makes working with queries as easy and smooth as ever. Code faster with snippets, IntelliSense-style code completion, instant formatting, smart refactoring, and a bunch of other useful features provided by the tool.

Rename a SQL Server database using detach and attach

SQL Server has an attach and detach feature that can be of use in terms of the purpose of this article. You can detach a database and then reattach it with a new name. This action can be performed with the help of a T-SQL query or you can do it in the comfort of a GUI tool. We will be using dbForge Studio for SQL Server for this purpose.

Detach and reattach using a T-SQL query

First of all, execute the following command to get the database file names:

EXEC sp_helpdb 'BicycleStore2007';

Then, use this query to detach the database:

EXEC sp_detach_db 'BicycleStore2007', 'true';

The next query will reattach the database with a different name:

EXEC sp_attach_db
@dbname = N'BicycleStore',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\BicycleStore2007.mdf',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\BicycleStore2007_log.ldf';

Detach and reattach in dbForge Studio for SQL Server

Alternatively, you can do the same thing using the graphic interface of dbForge Studio for SQL Server:

1. Right-click the database that you are willing to rename, point to Tasks, and click Detach Database.

2. In the Detach Database window that opens, choose whether you would like to drop the connections and/or update the statistics related to the database in question along with its detachment.

Note: You can also save these changes as a script to a new SQL window or to the clipboard for future use.

3. Click OK. If everything is okay, you will see a green indicator in the Status column. Finally, click Close.

4. Let us refresh the Database Explorer. As you can see in the screenshot below, there is no BicycleStore2007 database in the list. However, bear no worries as it is not gone forever. What we need to do now is bring it back with a new name – BicycleStore.

5. Right-click the active connection, point to Tasks, and click Attach Database.

6. In the Browse Files window that opens, select the BicycleStore2007.mdf file and click OK.

Note: Usually, dbForge Studio automatically opens the folder where all the SQL Server databases are stored by default. However, if the database you are working with is located somewhere else, you can specify the path to the corresponding folder. Also, you can filter the files by their type and enter the name of the file directly in the Browse Files window.

7. In the Attach As column of the Attach Database window type BicycleStore. If needed, select more databases to be added to this batch. If not, simply click OK. dbForge Studio will check if the action can be performed. If everything is okay, you will see a green indicator in the Status column. Finally, click Close.

8. On refreshing Database Explorer, you will see the renamed database is back again under the updated name – BicycleStore.

Rename a SQL Server database using SSMS

SQL Server Management Studio allows using T-SQL queries, detach and attach feature, and also offers another convenient way to rename a database.

All you need to do is select a database you would like to rename, left-click it, and enter a new name for it. Alternatively, right-click the database in question and hit Rename. This procedure is basically the same as renaming a regular file in Windows OS.

Conclusion

To sum up, there are many ways to rename a SQL Server database if you need it: you can use a T-SQL query either in the command line interface or in a convenient IDE of your choice. dbForge Studio for SQL Server allows SQL developers and DBAs to speed up and improve almost any database experience, including such simple things as database renaming. Download a fully-functional trial version of dbForge Studio for SQL Server for evaluation purposes.