Lesson Learned #128: How to track the automated backup for an Azure SQL Managed Instance

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.