Monitor Azure SQL Database Restore progress at more granular level
Published Nov 13 2022 06:42 PM 1,638 Views
Microsoft

Database Backup & Restore capabilities are crucial for ensuring Business continuity and Disaster recovery. Restore database operation is usually done in critical situations like hardware failure, application errors, ransomware attacks, accidental deletion of database etc., to restore a production database to latest known stable state. In such critical situations users would want to track the progress of restore operation 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 as follows:

  1. Portal displays whether the database restore is “Restoring”(restore in progress) or “Online”(restore is complete)
  2. Using T-SQL users can view database restore progress using sys.dm_operation_status dynamic management view (DMV). This DMV contains 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 scenarios where the size of backup to be restored is big, restore operations take time, especially in General Purpose and Business Critical service tiers where backups are file based and restore is a size of data operation. In such scenarios percent_complete column in Sys.dm_operation_status DMV would show 50% throughout the restore operation and did not give granular details of the progress. As a result, users did not have visibility into how far along the restore operation is complete and if it is actually progressing. 

 

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 only 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 and the range is not fixed for each state i.e., range of progress in percent is not fixed for each state and can vary based on the estimated work in each state of a restore operation. 

 

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

Please note the updated values for percent_complete column is applicable for Point-in-Time Restore (PITR) and Long-Term retention (LTR) backup restore operations in all service tiers of Azure SQL DB including Hyperscale.

 

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;

Please note:

  1. This view is only available in the master database to the server-level principal login. To use this view, you must be connected to the master database.
  2. The information in this view is retained for approximately 1 hour. Please use the Azure Activity Log to view details of operations in the last 90 days.

Limitations

  • This enhancement is not applicable for Azure SQL Managed Instance (MI).
  • Currently T-SQL is the only supported way to view accurate progress of a backup restore operation and there is no support via Portal, PowerShell, CLI or API.

 

Learn more about sys.dm_operation_status and updated percent_complete column.

Co-Authors
Version history
Last update:
‎Nov 13 2022 06:42 PM
Updated by: