SQL – Renaming a Database

By David Crowther

Question:

How do you rename a SQL Database?

Answer:

It is quite simple to rename a SQL Database. Using Sql Server Management Studio (SSMS), in the Object Explorer – you can right click on the Database and choose Rename to give it a new name.

However, if there are current connections to that Database SSMS will throw an error warning that the rename task cannot be done as the database cannot be locked exclusively.

To rename the Database, it therefore needs to be exclusively accessible to you the administrator.

To do this follow the steps below to put the Database into Single User Mode, where you can then Rename the Database and then set it back to Multiuser mode.

1. Set the database to Singleuser Mode
   

   ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2. Rename the Database
   

   ALTER DATABASE DatabaseName MODIFY NAME = NewDatabaseName

3. Set the Database to Multiuser Mode
   

   ALTER DATABASE NewDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE

The steps above will now allow you to successfully rename your database and make it available again for all your users.