Database Backup & Restore capabilities are crucial for ensuring Business continuity and Disaster recovery. Restore database operation is usually done in critical situations where a user is trying to restore a database to a stable/known state. In such critical situations users would want to track the progress accurately so that they can plan for subsequent actions and/or alternatives.
Currently in Azure SQL DB, you can view the database restore progress either using Portal or using T-SQL by querying a DMV called sys. dm_operation_status. Both portal and DMV currently display restore progress as follows:
- Portal displays whether the database restore is “Restoring”(restore in progress) or “Online”(restore is complete)
- Sys.dm_operations_status DMV has a column called percent_complete which displays 3 states:
- 0 = Operation not started
- 50 = Operation in progress
- 100 = Operation complete
This information is useful to understand whether the restore operation is in progress, but in many scenarios where the size of operation is big and time consuming, this information does not give granular details of the progress and how much more time is the operation expected to take. As a result, users start to second guess if the operation is running successfully.
To solve this problem, percent_column in sys.dm_operation_status DMV has been enhanced to display the progress of Database Restore operation at a more granular level. percent_complete column in sys.dm_operation_status DMV will now show percent progress as a continuous value from 0 to 99 instead of 50 earlier. Restore is a multi-phase process and state_desc column in sys.dm_operation_status DMV contains phase information. Percent progress range is distributed across these phases based on the size of operations in each phase.
New values for percent_complete column for Database Restore operation are as follows:
- 0 = Operation not started
- 1-99 = Operation in progress and indicates how far along the operation is, in percent.
- 100 = Operation complete
Here is a sample T-SQL query to view the status of a Restore operation:
Select * from sys.dm_operation_status
where operation like '%Restore%'
order by Start_Time Desc;
Current Preview Limitations
- Please note the updated values for percent_complete column is applicable for Restore Database operation only in Azure SQL DB all tiers including Hyperscale. It is not applicable for Azure SQL Managed Instance(MI).
- Currently T-SQL is the only supported way to view accurate progress for restore operation. Updates to Portal, PowerShell and CLI are on Roadmap.
Learn more about sys.dm_operation_status and updated percent_complete column.
