How to rename/delete Azure SQL DB

Question

Hi,

I'm trying to rename/delete our existing dev SQL Azure DB but I seem to be getting the following error:

Any ideas what I need to do please?

Thanks

Answer 1

@T West Thank you for reaching out.

You can rename user created databases only and not the system databases.

Renaming a user created database is not supported from the Azure Portal.
However, you can easily do this using SSMS.

If you have any active connections on the database, you will need to kill them using the below script before renaming

DECLARE @kill varchar(8000) = '';  
  
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'  
  
FROM sys.dm_exec_connections AS c  
JOIN sys.dm_exec_sessions AS s  
    ON c.session_id = s.session_id  
WHERE c.session_id <> @@SPID  
--AND status = 'sleeping'  
ORDER BY c.connect_time ASC  
  
EXEC(@kill)  

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

Answer 2

HI @T West
Renaming Database - Limitations and Restrictions
System databases cannot be renamed.
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.
In Azure SQL Database, you must make sure no other users have an open connection to the database to be renamed.

Rename with command-line – TSQL

1. Connect with SQL Server Management Studio to your Azure database server
2. Right-click on the master database and select New Query
3. In the New Query window type ALTER DATABASE [dbname] MODIFY NAME = [newdbname]. (Make sure you include the square brackets around both database names.)

Delete a Database
Create, Copy, Rename And Delete SQL Database In Azure

If the Answer is helpful, please click Accept Answer and up-vote, this can be beneficial to other community members.