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

Leave a Reply Cancel reply