How to rename SQL Database to remove space

Question

Rename it from SQL Tutorial to SQLTutorial.

I've tried using Rename from Object Explorer to no avail

Answer 1

Hi @Sara Saleh

I've tried using Rename from Object Explorer to no avail

The database name cannot be changed while other users are accessing the database. In SQL Server, you can set a database in single user mode to close any open connections. For more information, see set the database to single-user mode. Also, it requires ALTER permission on the database.

Use the following steps to rename a SQL Server database using T-SQL in SQL Server Management Studio including the steps to place the database in single-user mode and, after the rename, place the database back in multi-user mode.

1. Connect to the master database for your instance.
2. Open a query window.
3. Copy and paste the following example into the query window and select Execute.
4. Optionally, if the database was your default database, see Reset your default database after rename.

USE master;  
GO  
ALTER DATABASE "SQL Tutorial" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE "SQL Tutorial" MODIFY NAME = SqlTutorial;
GO  
ALTER DATABASE SqlTutorial SET MULTI_USER;
GO

Note: To quickly obtain exclusive access, the code sample uses the termination option WITH ROLLBACK IMMEDIATE. This will cause all incomplete transactions to be rolled back and any other connections to the "SQL Tutorial" database to be immediately disconnected.

Please refer to this tech doc for more details: Rename a Database.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

ALTER DATABASE "SQL Tutorial" MODIFY NAME = SqlTutorial

Answer 3

Follow the below steps to rename the SQL database with SSMS.

The steps are:

1. Open Object Explorer in SSMS.
2. Right Click on the database and click on properties.
3. In the Properties, select the Options page
4. From the Restrict Access option, select Single.
5. Again, right-click the Database and click on Rename.
6. Enter a new name for database and hit enter.
7. Again, open Options page in properties and change Restrict Access to MULTI_USER

Rename SQL Database with Query:

Use ALTER DATABASE query to rename a database in SQL Server. The renaming process will fail, in case there are any active connections established by applications or other databases.

Use the below queries to rename the database with SQL query. The first query will change the database to single-user mode to prevent any external connection. After that rename, the database and again set the database to multi-user mode.

The database rename queries will be like:

ALTER DATABASE SQL Tutorial SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO
EXEC sp_renamedb 'SQL Tutorial', 'SQLTutorial'
GO
ALTER DATABASE SQLTutorial SET MULTI_USER;
GO