AzureFeeds

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:

1. Querying the msdb.dbo.backupset system table


2. Identifying the latest full (type = ‘D’) and differential (type = ‘I’) backups


3. Comparing timestamps against expected frequency thresholds:




• Full backup: every 7 days


• Differential backup: every 12 hours




4. Sending email notifications using sp_send_dbmail


5. 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

/* ——————————————————————
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) = ”; — ✅ 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:

1. Open SQL Server Management Studio (SSMS)


2. Go to SQL Server Agent > Jobs > New Job


3. General Tab:




• Name: Backup Monitor – Success and Failures


• Owner: Select the owner for the job.




4. Steps Tab:




• Step Name: Check Backups


• Type: Transact-SQL Script (T-SQL)


• Database: msdb


• Paste the script into the command window




5. Schedules Tab:




• Schedule Name: Every 12 Hours


• Frequency: Recurs every day, repeats every 12 hours


• Enable the schedule




6. Notifications Tab (Optional):




• You may configure job failure alerts here (though script already sends custom alerts)




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