How to Rename a SQL Server database?

Sometimes we need to change database name because the original name was based on the project that has become irrelevant to the data stored into the database or you had given temporarily name before and now you want to modify it. Irrespective of the reasons behind renaming the database, in this article, we will come up with ways about how to rename the database, what error may arise while doing so and how to fix them.

So to rename the database, you need to follow one of the methods as discussed below. First of all, we have to create a database which needs to be renamed.

Database Creation:

Select any database in your SQL Server Management System (SSMS). If you don’t have any you can create one by following this procedure.

1. Right-click on “Database” in the “Object Explorer” and select option “New Database”
2. A window will appear, in the left pane
3. select “General” type database name into the right pan and press “ok”. The database will be created

   

There are multiple methods to rename a database in SQL Server, which are discussed below in detail along with versions supported by any specific method. Later on, we will also discuss errors that arise while renaming the database, and procedure to fix them.

Method 1: Using SSMS rename option to rename the SQL Server Database

This is the simplest way to rename a database. You have to proceed like this to do so.

1. Right-click on database name from object explorer
2. Select “rename”, type database name, and press “enter”

Method 2: Using SSMS to rename the SQL Server Database

Another simplest way to rename a database just like renaming any folder in the windows.  You have to continue like this to do so. Click on the database to rename it just like renaming windows folders.

Method 3: Using T-SQL to rename the SQL Server Database

For SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017, this command works. Execute the following statement.

ALTER DATABASE [test] MODIFY NAME = [test_cases]

The output will be “The database name ‘test_cases’ has been set.” As seen in the figure below.

You can use the T-SQL command below to change the name of the database if you are using SQL Server 2000. With SQL 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017, this still works, but at some stage, Microsoft claims it will be phased out.

Method 4: Using detach and attach rename the SQL Server

Using SQL Server’s detach and attach feature can be used to remove the database first and assign the database a different name when you re-attach the database. The following T-SQL commands can be used to do this

Detaching the database by executing the following code:

EXEC sp_detach_db 'test', 'true'

The output will be like this

Attaching the database.

EXEC sp_attach_db @dbname = N'test',  
    @filename1 =  
N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test.mdf',  
    @filename2 =  
N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_log.ldf';

The output will be:

Using SSMS to detach and reattach databases

This can also be achieved using SSMS by following these steps.

1. Right-click the database, select “task” then click on “Detach”
2. Now click on “ok”

   

3. Now to attach the database right click on “Databases” in “Object Explorer” and click “Attach”. A screen will appear, click on Add. Another window will appear with database names in it as shown below select “test”.

   

4. But we want to add it as modified database name “test_cases” so type “test_case” in ”Attach as” box. Now like this. This will rename your database.

Furthermore, we will discuss errors that may arise while renaming the database. You will need exclusive access to the database to rename the database, which ensures that the database does not have any other database connections. But if this is not the case, it can result in errors as shown below. The error can even arise when we accidentally open another window with the same database name which has to be renamed.

Errors that arise while renaming the database via SSMS and by executing the query:

These below mentioned two errors have two different views because they arise while renaming the database using two different methods. Error 1 arises while renaming the database using SSMS and the second error arises while renaming the database using query. Both errors show the same message  “The database could not be exclusively locked to perform the operation”, that means the error arises as the user is not given exclusive access to the database to rename a database. So to rename the database we need to set the database in “single-user mode“.

Error 1: Error arises while renaming the database via SSMS

“Unable to rename test. (ObjectExplorer)
Additional information:
Rename failed for Database test (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)”

Error 2: Error arises while renaming the database using query

 “Msg 5030, Level 16, State 2, Line 2. The database could not be exclusively locked to perform the operation.”

First of all, we will reproduce the above-mentioned error messages for understanding that in which scenario they arise and how to fix them.

1. Right-click database name from “Object Explorer”
2. Select rename and type database name and press “enter” if it works its fine but if it does not work and results in an error like this “Unable to rename <database_name> (ObjectExplorer). The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)” as seen in the figure below, then you don’t have exclusive access to the database to rename a database.

   

3. This error means that the SQL Server will not allow the database to be renamed until unless it is in “single-user mode”.
4. So you will need exclusive access to the database to rename a database, to understand this open another query window and select database “test”

   

5. Now in the first window try to execute the following code.

6. ALTER DATABASE [test] MODIFY NAME = [test_cases]

7. But this will prone to an error message like this: “Msg 5030, Level 16, State 2, Line 2. The database could not be exclusively locked to perform the operation.”

Solution:

This will configure the database to roll back any pending transactions and set it in “single user mode” and then back into “multi-user mode”.

1. To fix this we need to close all other windows in which the same database name is being used or to set the database in “single user mode” by using the following commands.

2. Alter database test set single_user with rollback immediate
   go
   EXEC sp_renamedb 'test', 'test_cases'
   go
   alter database test_cases set multi_user
   go

3. The output will be like this: “Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. The database name ‘test_cases’ has been set.”

Conclusion:

These are multiple ways to rename the database. If one way is not working for you. You can go to another one. One thing that should be kept in mind that changing database names using these ways just rename the database. The “physical files” still have the same names. As we can see in the figure below we have changed the database name from “test” to “test_cases” but in the physical location, it remained the same.

So the simplest approach is to use solution 4 if you want to change the file name as well. You must first change the name of the physical files before reattaching the files and then specify the renamed files when you do the reattach.

Moreover, in addition to changing the names of the databases, you also need to check if there are any references in your application code to the database name.  This can be either within SQL Server or outside of SQL Server.