How to Monitor Automated Backups in Azure SQL Managed Instance Using T-SQL and SQL Agent
Azure SQL Managed Instance simplifies database operations by automating backups for full, differential, and transaction log types—ensuring point-in-time recovery and business continuity. These backups are managed entirely by the Azure platform, which provides reliability and peace of mind for most operational scenarios. For deeper insights and historical tracking, backup metadata is logged internally in system tables like msdb.dbo.backupset and msdb.dbo.backupmediafamily, which users can query directly. This article demonstrates how to build a lightweight, T-SQL-based alerting mechanism that allows you to track full and differential backup activity using native system views and SQL Agent automation. Problem Scenario While Azure SQL Managed Instance takes care of backups automatically, customers with specific audit and compliance requirements often ask: Can I get alerted if a backup hasn't happened recently? Can I prove that full and differential backups are consistently occurring? Is there a way to build proactive visibility into backup activity without relying on manual checks? This solution provides a way to address those questions using built-in system views and automation features available in SQL MI. Pre-requisites For this use case I assume you already have your DB mail profile configured. You can refer to the following documentations to configure the database mail on SQL Managed instance. Ref: Sending emails in Azure SQL Managed Instance | Microsoft Community Hub Job automation with SQL Agent jobs - Azure SQL Managed Instance | Microsoft Learn SQL Server agent enabled on SQL Managed Instance Access to msdb database: you’ll query backup history tables like backupset, backupmediafamily. Permissions: you need a login with sysadmin or at least access to msdb and permission to create jobs. Strategy of the Solution The monitoring approach consists of: Querying the msdb.dbo.backupset system table Identifying the latest full (type = 'D') and differential (type = 'I') backups Comparing timestamps against expected frequency thresholds: Full backup: every 7 days Differential backup: every 12 hours Sending email notifications using sp_send_dbmail Scheduling the check via a SQL Server Agent job that runs every 12 hours This solution is flexible, auditable, and requires no external components. Summary of Script Logic Loop through a list of databases (default: database1) For each DB: Get most recent full backup; if within 7 days, send success email; otherwise, send failure alert Get most recent differential backup; if within 12 hours, send success email; otherwise, send failure alert Uses sp_send_dbmail to notify recipients Alert Frequency: The job runs every 12 hours and generates up to 2 emails per database per run (1 for full, 1 for differential backup) T-SQL Script USE msdb; GO /* ------------------------------------------------------------------ Script: Backup Monitoring for Azure SQL Managed Instance Purpose: Notify for both successful and failed full/differential backups Frequency: Full = Weekly, Differential = Every 12 hours ------------------------------------------------------------------ */ -- Declare DB name(s). Add more databases here if needed DECLARE @Databases TABLE (DbName NVARCHAR(128)); INSERT INTO @Databases (DbName) VALUES ('database1'); -- Add more like: ('database2'), ('database3') -- Setup email parameters DECLARE @MailProfile SYSNAME = 'AzureManagedInstance_dbmail_profile'; -- ✅ Your Database Mail Profile DECLARE @Recipients NVARCHAR(MAX) = '<recipients_mail@yourdomain.com>'; -- ✅ Your email address DECLARE @Now DATETIME = GETDATE(); -- Loop through databases DECLARE @DbName NVARCHAR(128); DECLARE DbCursor CURSOR FOR SELECT DbName FROM @Databases; OPEN DbCursor; FETCH NEXT FROM DbCursor INTO @DbName; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Subject NVARCHAR(200); DECLARE NVARCHAR(MAX); ----------------------------- -- 🔍 1. Check FULL BACKUP ----------------------------- DECLARE @LastFullBackup DATETIME; SELECT @LastFullBackup = MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = @DbName AND type = 'D'; -- Full backup IF @LastFullBackup IS NOT NULL AND @LastFullBackup > DATEADD(DAY, -7, @Now) BEGIN -- ✅ Full backup success SET @Subject = 'SUCCESS: Full Backup Taken for ' + @DbName; SET = '✅ A full backup was successfully taken for "' + @DbName + '" on ' + CONVERT(VARCHAR, @LastFullBackup, 120); END ELSE BEGIN -- ❌ Full backup failed SET @Subject = 'FAILURE: No Full Backup in Last 7 Days for ' + @DbName; SET = '❌ No full backup has been taken for "' + @DbName + '" in the past 7 days. Please investigate.'; END EXEC msdb.dbo.sp_send_dbmail = @MailProfile, @recipients = @Recipients, @subject = @Subject, = ; ---------------------------------- -- 🔍 2. Check DIFFERENTIAL BACKUP ---------------------------------- DECLARE @LastDiffBackup DATETIME; SELECT @LastDiffBackup = MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = @DbName AND type = 'I'; -- Differential backup IF @LastDiffBackup IS NOT NULL AND @LastDiffBackup > DATEADD(HOUR, -12, @Now) BEGIN -- ✅ Differential backup success SET @Subject = 'SUCCESS: Differential Backup Taken for ' + @DbName; SET = '✅ A differential backup was successfully taken for "' + @DbName + '" on ' + CONVERT(VARCHAR, @LastDiffBackup, 120); END ELSE BEGIN -- ❌ Differential backup failed SET @Subject = 'FAILURE: No Differential Backup in Last 12 Hours for ' + @DbName; SET = '❌ No differential backup has been taken for "' + @DbName + '" in the past 12 hours. Please investigate.'; END EXEC msdb.dbo.sp_send_dbmail = @MailProfile, @recipients = @Recipients, @subject = @Subject, = ; FETCH NEXT FROM DbCursor INTO @DbName; END; CLOSE DbCursor; DEALLOCATE DbCursor; SQL Server Agent Job Setup To automate the script execution every 12 hours, configure the job as follows: Open SQL Server Management Studio (SSMS) Go to SQL Server Agent > Jobs > New Job General Tab: Name: Backup Monitor - Success and Failures Owner: Select the owner for the job. Steps Tab: Step Name: Check Backups Type: Transact-SQL Script (T-SQL) Database: msdb Paste the script into the command window Schedules Tab: Schedule Name: Every 12 Hours Frequency: Recurs every day, repeats every 12 hours Enable the schedule Notifications Tab (Optional): You may configure job failure alerts here (though script already sends custom alerts) Click OK to save and activate the job Ref here for more details: Create a SQL Server Agent Job in SSMS | Microsoft Learn This script provides a method for monitoring Azure SQL Managed Instance backups, with email notifications to flag both success and failures. It enables teams to proactively manage backup health, meet audit requirements, and ensure backups occur as expected. Improvements & Extensions Want to take it further? Here are next-level ideas: Write backup check results to a custom audit table for history and dashboards (I will try to cover this in my next article) Extend monitoring to transaction log backups (type = 'L') Build a Power BI or SSRS dashboard on top of the log table Useful Documentation Automated Backup Frequency in SQL MI Monitor Backup Activity in SQL MI
