Restored Database stuck

Richard Brynteson 35 Reputation points MVP
2021-10-05T14:45:27.69+00:00

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

Azure SQL Database
0 comments
Accepted answer
  1. Alberto Morillo 32,806 Reputation points MVP
    2021-10-05T17:00:06.973+00:00

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Richard Brynteson 35 Reputation points MVP
    2021-10-08T00:00:02.773+00:00

    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

    0 comments