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