Forum Discussion
Solved
60MB Azure SQL database restore taking over 50 minutes?
I kicked off a restore of an Azure SQL Database via the Azure Portal. Its only 60MB in size and is LRS so expected it to be pretty quick. 50+ minutes later it is still "Restoring..." wondered is ...
- In defence of Azure just a bit, the on-prem (private cloud) while more under our control and not shared with thousands of other customers, can have the same issue. Any type of restore would have to take in consideration the weekend full backups.
The solution mentioned above with an Azure VM running SQL is expensive as you said, so your concern would be a good one to raise with your Microsoft account executive and customer success account manager. Both likely would want to know that you provisioned a VM to resolve a concern related to an unmet Azure SQL RTO (Recovery Time Objective) and/or RPO (Recovery Point Objective).
Same here. I'm trying to restore a 90mb database, and it is now running for 40 minutes.
There is not even a progress or an estimation shown in the azure portal.
This whole process should be enhanced
There is not even a progress or an estimation shown in the azure portal.
This whole process should be enhanced
While waiting on a long database restore from Azure consider trying a second concurrent restore in this way:
CREATE DATABASE new_database AS COPY OF existing_database;
The destination database server (if different SQL Servers) must have the same server admin name and password as the source database server OR the two SQL Servers must have same Microsoft Entra admin security group or named user.
What I have observed is that while the first restore request is making no progress, a second restore request can have the expected duration of just minutes for a small, 90mb, database.
From a second query window on the destination SQL Server, the below can be can be helpful for monitoring the progress of the restore.
SELECT
session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE','CREATE DATABASE') ;
SELECT * FROM sys.dm_operation_status
WHERE major_resource_id = '<new_database>'
ORDER BY start_time DESC;
CREATE DATABASE new_database AS COPY OF existing_database;
The destination database server (if different SQL Servers) must have the same server admin name and password as the source database server OR the two SQL Servers must have same Microsoft Entra admin security group or named user.
What I have observed is that while the first restore request is making no progress, a second restore request can have the expected duration of just minutes for a small, 90mb, database.
From a second query window on the destination SQL Server, the below can be can be helpful for monitoring the progress of the restore.
SELECT
session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE','CREATE DATABASE') ;
SELECT * FROM sys.dm_operation_status
WHERE major_resource_id = '<new_database>'
ORDER BY start_time DESC;