How to rename MS SQL Server Database in SSMS

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.

Renaming database via the context menu

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

Error message during renaming the database
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
    


6x faster WordPress Hosting6x faster WordPress Hosting

Leave a Reply

Your email address will not be published. Required fields are marked *

five × five =