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
SQL Server Agent fails to start on Windows Server 2022 due to Application event log policy setting
Problem A customer recently reported that they tried to build a SQL Server running on Windows Server 2022 but found that the SQL Server Agent failed to start. Below is the error popup when starting the SQL Server Agent in SQL Server Configuration Manager. There are no other errors in ERRORLOG, SQLAGENT, and Windows event logs. We worked with the customer and collected TTD (Time Travel Debugging) traces on several services. Finally, we found out the cause of the problem. Cause In Windows, there is a group policy named “Configure log access” under “Computer Configuration” -> “Administrative Templates” -> “Windows Components” -> “Event Log Service” -> “Application”. You can use this policy to set an SDDL (Security Descriptor Definition Language) string to grant users access to the Application event log. An example of the SDDL string that can be used here is “O:BAG:SYD:(A;;0xf0005;;;SY)(A;;0x7;;;BA)(A;;0x1;;;S-1-5-32-573)”, for which the parts in the 3 parentheses grant the following accesses: (A;;0xf0005;;;SY) grants Full Control to the Local System account. (A;;0x7;;;BA) grants read and write access to the Built-in Administrators group. (A;;0x1;;;S-1-5-32-573) grants read access to the Event Log Readers group. However, there was a problem in old versions of Windows up to Windows Server 2019 where this policy had not been strictly enforced. As a result, a user account not granted write access to the Application event log by this policy could still write to the said event log. In the versions of Windows released after Windows Server 2019, this problem has been fixed. Therefore, the restriction of access to the Application event log by this policy is correctly enforced. This has caused the problem of SQL Server Agent not starting on Windows Server 2022 in our case. Upon service start, the SQL Server Agent calls a Windows API to register its own event source to the Application event log, which requires write access to it. Because the SQL Server Agent runs under an account that is not granted write access to the Application event log by the policy, the API call fails and SQL Server Agent quits itself because of the error. Solution There are two ways to solve this problem: Set the “Configure log access” policy to “Not Configured” or “Disabled”. If you need to enable the “Configure log access” policy, then you need to grant both read and write access to the account used by the SQL Server Agent service by adding the SDDL string for that account. The additional SDDL string should start with A;;0x7;;; and end with the SID string for that account. You should also add parentheses around the SDDL string. As an example, the SID for the “NT Service\SQLSERVERAGENT” is S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430. The SDDL string that needed to be added is (A;;0x7;;;S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) . Below is the whole SDDL string after this addition: O:BAG:SYD:(A;;0xf0005;;;SY)(A;;0x7;;;BA)(A;;0x1;;;S-1-5-32-573)(A;;0x7;;;S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) How to check if you are hitting the same problem 1. The SQL Server Agent fails to start, but there are no errors logged in ERRORLOG, SQLAgent logs and event logs 2. The “Configure log access” policy under “Computer Configuration” -> “Administrative Templates” -> “Windows Components” -> “Event Log Service” -> “Application” is enabled. You can check this policy by running gpedit.msc. Other known issues with SQL Server agent not starting https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/startup-shutdown/agent-service-fails-start-stand-alone-server https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/startup-shutdown/sql-server-agent-crashes-upon-start Thank you!!
