Lesson Learned #128: How to track the automated backup for an Azure SQL Managed Instance
Published Jun 05 2020 01:48 AM 4,449 Views
In this last week we received a question about how to track the automated backup performed in Azure SQL Managed Instance. Besides the error log that you could see the details of the different backups I would like to share another way to track them. 
 
I would like to suggest the creation of an extended event that saves in an extended file all the backups done.
 
Basically, we need to create a credential:
 
CREATE CREDENTIAL [https://myblobstorage.blob.core.windows.net/backup] 
WITH IDENTITY='SHARED ACCESS SIGNATURE', 
SECRET = 'sv=2019-10-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2020-05-28T02:42:57Z&st=2020-05-20T18:42:57Z&spr=https&sig=1rC1tEiQOtUM%3D' 
 
And create the following the following extended event that will save a line everytime that a backup is done. 
CREATE EVENT SESSION [Backup] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    WHERE ([operation_type]=(0) AND [trace_message] like '%100 percent%'))
 ADD TARGET package0.asynchronous_file_target(
     SET filename='https://myblobstorage.blob.core.windows.net/backup/backup.xel')
GO
 
Also, remember that in Azure SQL Managed Instance is possible to run xp_readerrorlog, for example, running this command to obtain the backup operations EXEC master.dbo.xp_readerrorlog 0, 1, N'backup', NULL, N'2020-01-02', N'2020-12-02', N'desc' and send an email using Database Email 
 
Enjoy!!
1 Comment
Version history
Last update:
‎Jun 05 2020 01:48 AM
Updated by: