Tag: MS SQL Server

This article describes how to rename database in Microsoft SQL Server Management Studio.

Problem:

I wanted to rename one MS SQL database in MS SQL Management Studio via the context menu.

After typing the new name and pressing Enter I got the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Unable to rename TESTDB_1. (ObjectExplorer)
------------------------------
ADDITIONAL INFORMATION:
Rename failed for Database 'TESTDB'.  (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)
------------------------------

Then I tried to rename with the following SQL statement:

ALTER DATABASE TESTDB
MODIFY NAME = TESTDB_1

In this case I got this error:

The database could not be exclusively locked to perform the operation.

Solution:

After some search on the Internet I found information, that I got this error, because the database is in multi-user mode and has some open connection.

Here is the solution which worked for me:

1. At first need to change the database to single-user mode:

   ALTER DATABASE TESTDB
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   




2. Then try to rename the database:

   ALTER DATABASE TESTDB
   MODIFY NAME = TESTDB_1
   




3. Finally, set back the database to multi-user mode:

   ALTER DATABASE TESTDB_1
   SET MULTI_USER WITH ROLLBACK IMMEDIATE