question

RichardBrynteson-2467 avatar image
0 Votes"
RichardBrynteson-2467 asked RichardBrynteson-2467 answered

Restored Database stuck

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered SaurabhSharma-msft edited

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @richardbrynteson-2467,

Following up to know if you are still having issues. Also, in which region your server is ?

Thanks
Saurabh

0 Votes 0 ·

Hi @richardbrynteson-2467,
As per internal discussion it looks like an issue with Basic, S0, S1 tiers where restoration service is having some connectivity issues with the SQL instance which products team is currently working on to provide a fix. In the interim if you are using any of these tiers then the best\quickest mitigation is to to restore as an S3 and immediately down-grade it back to the desired target. Hope this helps to resolve your issue.
Also, I suggest you to please open a Support ticket so that products team could dig into any specific issues. In case you have any limitations opening a support please let me know.

Apologies for your inconvenience.

Thanks
Saurabh

0 Votes 0 ·
RichardBrynteson-2467 avatar image
0 Votes"
RichardBrynteson-2467 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.