Sql-server – Azure Sql Database stuck “Restoring”

azuredatabasesql server

I aborted the restore of an Azure Sql database over 2 weeks ago. While no longer visible in the Azure Portal it continues to appear in the list of database In Sql Server Management Studio. "(Restoring…)" appears after the database name. Similarly it appears in the list returned buy Select * from sys.databases. However Drop Database returns 

Msg 911, Level 16, State 1, Line 8<br/>
Database '<db name>' does not exist.

How can I get rid of it?

Best Answer

You could try if USE [master]; ALTER DATABASE [<db name>] SET ONLINE brings the database out of restoring state but that might not work in yout case.

You may need to bring the database to a working state at first and therefor restore it by using the WITH RECOVERY option, with your database RESTORE command, to bring your database online as part of the restore process.

That can be done using the restore database wizard in SQL Server Management Studio. This way you can select the overwrite option, and the WITH Recovery option.

If neither of those work Azure PowerShell will be your last chance:

Remove-AzSqlInstanceDatabase -Name "Database01" -InstanceName "managedInstance1" -ResourceGroupName "ResourceGroup01" -Force

In an on premise environment you could stop the SQL Server Service, rename or delete the DB and Log files, start the SQL Service to being able to delete the (then failed) database.

Related Topic