Restored Database stuck

Question

Attempted to do a restore of a database in SQL Azure. It stopped midway through in a failure. In SQL Management studio I can see database listed as DBName_2021_09_29T17-31Z (Restoring...) but there are no active jobs running anymore. It's been in this state for over 24 hours. From the Azure Portal I cannot see this database.

If I attempt to right-click, browse, open the database in this restoring state I get an error about SSL Handshake. Any secret, CLI way to go in, see what databases are actually deployed to this SQL Server and delete if necessary?

Issue is, I now can't restore any other database, they always fail.

Thanks,

Richard

Answer 1

You can run the following query on the Master database of the Azure SQL Database server to track the progress of a restore.

SELECT major_resource_id, percent_complete  
FROM sys.dm_operation_status   
WHERE operation LIKE '%DATABASE RESTORE%'  

If you don't see any restore then trigger a new one. If you don't see progress on the restore over time, create a support ticket. As you can see here, some customers report restore backups taking more than 72 hours to finish.

Remember it is not possible to restore a database replacing an existent Azure SQL database. You wil have to restore a database with a new name. To restore a database with the same name of an existent database you will have to drop the existent database first.

Answer 2

If finally finished after nearly 70 hours to restore 2GB. Something was off but at least it's done now. I got what I needed and will remove it.

Thanks for the SQL query to check status. It was perfect.

Richard