How to check how long a DB restore took on Azure SQL Managed Instance?

Question

I have just completed a DR test overnight to run a PITR on Azure SQL Managed Instance. This was for a fairly large DB (4TB). I did this by using the Azure CLI. Once I executed the command (see below) and made sure there were no errors, I exited the CLI.

This morning the restore worked successfully, but I am struggling to find the metrics for the time it took to restore. Does anyone know where I can find these metrics?

I have checked the audit logs with no luck, looked at the DB created date in SQL, so I have a rough indication, but I want solid numbers I can reference in this DR report.

# Restore script I ran
az sql midb restore -g sourceRG --managed-instance sourceMi --name sourceMiDb --dest-resource-group destinationRG --dest-mi destinationMi --dest-name destinationMiDb --time "2019-07-21T10:00:00" --verbose --debug

[timing in ps](https://mcpmag.com/articles/2016/03/31/timing-powershell-automations.aspx) — lloyd, Jul 24 '19 at 05:27

score 1 · Answer 1 · answered Aug 09 '19 at 16:16

Managed Instance is a bit tricky, since everything is so obfuscated. For a database to become available on a MI, it needs to be both recovered & also replicated to the secondary nodes.

To find out when the database recovered, check out Thomas LaRock's script here: https://www.mssqltips.com/sqlservertip/1724/when-was-the-last-time-your-sql-server-database-was-restored/

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type 
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From], 
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

As for determining when the database is replicated to the secondary nodes...that's tougher to tell. For a DB restore from BLOB storage, it seems the db will first restore, then replicate to the other AG nodes. However, when using Azure CLI like your example... I'm not so sure. My guess is they restore in parallel on each AG node, so it's available the moment the last log file is applied.

Everything you see in the logs will refer to a guid, so to figure out your DB guid you'll need to query sys.databases

SELECT physical_database_name, name FROM sys.databases

How to check how long a DB restore took on Azure SQL Managed Instance?

1 Answers1