%3CLINGO-SUB%20id%3D%22lingo-sub-1442355%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23128%3A%20How%20to%20track%20the%20automated%20backup%20for%20an%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442355%22%20slang%3D%22en-US%22%3E%3CDIV%3EIn%20this%20last%20week%20we%20received%20a%20question%20about%20how%20to%20track%20the%20automated%20backup%20performed%20in%20Azure%20SQL%20Managed%20Instance.%20Besides%20the%20error%20log%20that%20you%20could%20see%20the%20details%20of%20the%20different%20backups%20I%20would%20like%20to%20share%20another%20way%20to%20track%20them.%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EI%20would%20like%20to%20suggest%20the%20creation%20of%20an%20extended%20event%20that%20saves%20in%20an%20extended%20file%20all%20the%20backups%20done.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EBasically%2C%20we%20need%20to%20create%20a%20credential%3A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20CREDENTIAL%20%5Bhttps%3A%2F%2Fmyblobstorage.blob.core.windows.net%2Fbackup%5D%26nbsp%3B%0AWITH%20IDENTITY%3D'SHARED%20ACCESS%20SIGNATURE'%2C%26nbsp%3B%0ASECRET%20%3D%20'sv%3D2019-10-10%26amp%3Bss%3Dbfqt%26amp%3Bsrt%3Dsco%26amp%3Bsp%3Drwdlacupx%26amp%3Bse%3D2020-05-28T02%3A42%3A57Z%26amp%3Bst%3D2020-05-20T18%3A42%3A57Z%26amp%3Bspr%3Dhttps%26amp%3Bsig%3D1rC1tEiQOtUM%253D'%26nbsp%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EAnd%20create%20the%20following%20the%20following%20extended%20event%20that%20will%20save%20a%20line%20everytime%20that%20a%20backup%20is%20done.%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20EVENT%20SESSION%20%5BBackup%5D%20ON%20SERVER%26nbsp%3B%0AADD%20EVENT%20sqlserver.backup_restore_progress_trace(%0A%26nbsp%3B%20%26nbsp%3B%20WHERE%20(%5Boperation_type%5D%3D(0)%20AND%20%5Btrace_message%5D%20like%20'%25100%20percent%25'))%0A%26nbsp%3BADD%20TARGET%20package0.asynchronous_file_target(%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSET%20filename%3D'https%3A%2F%2Fmyblobstorage.blob.core.windows.net%2Fbackup%2Fbackup.xel')%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EAlso%2C%20remember%20that%20in%20Azure%20SQL%20Managed%20Instance%20is%20possible%20to%20run%20xp_readerrorlog%2C%20for%20example%2C%20running%20this%20command%20to%20obtain%20the%20backup%20operations%26nbsp%3B%3CSTRONG%3EEXEC%20master.dbo.xp_readerrorlog%200%2C%201%2C%20N'backup'%2C%20NULL%2C%20N'2020-01-02'%2C%20N'2020-12-02'%2C%20N'desc'%3C%2FSTRONG%3E%20and%20send%20an%20email%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sql-database%2Fsending-emails-in-azure-sql-managed-instance%2Fba-p%2F386235%23%3A~%3Atext%3DAzure%2520SQL%2520Database%2520Managed%2520Instance%2Cemails%2520directly%2520from%2520Managed%2520Instance.%22%20target%3D%22_self%22%3EDatabase%20Email%3C%2FA%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EEnjoy!!%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1442355%22%20slang%3D%22en-US%22%3E%3CDIV%3EIn%20this%20last%20week%20we%20received%20a%20question%20about%20how%20to%20track%20the%20automated%20backup%20performed%20in%20Azure%20SQL%20Managed%20Instance.%20Besides%20the%20error%20log%20that%20you%20could%20see%20the%20details%20of%20the%20different%20backups%20I%20would%20like%20to%20share%20another%20way%20to%20track%20them.%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443346%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%23128%3A%20How%20to%20track%20the%20automated%20backup%20for%20an%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443346%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20looks%20like%20it%20could%20help.%26nbsp%3B%20Question%3A%20how%20does%20the%20credential%20created%20on%20the%20blob%20storage%20relate%20back%20to%20the%20creation%20of%20the%20extended%20event%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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
Senior Member

This looks like it could help.  Question: how does the credential created on the blob storage relate back to the creation of the extended event?