Tells SQL Server Agent to use Database Mail (profile SQL_Alerting) so it can send alert and job-failure emails. Set up Database Mail first, then run this and restart SQL Server Agent. Windows only; on Linux use mssql-conf. Tested on SQL Server 2017 and 2025 and safe to run more than once.

-- 06.Setup_SQL_Server_Agent_Alerting_System.sql
-- Author: Clive Roberts DBA. For SQL Server 2016 to 2022 (tested on SQL Server 2017 and 2025). Safe to run again.
-- Change the settings below if needed, then run it (F5, normal mode, not SQLCMD).
IF OBJECT_ID('tempdb..#InstallSettings') IS NOT NULL DROP TABLE #InstallSettings;
CREATE TABLE #InstallSettings (name sysname PRIMARY KEY, value nvarchar(400) NOT NULL);
INSERT INTO #InstallSettings (name, value) VALUES
    (N'MailProfile',           N'SQL_Alerting');                      -- Database Mail profile name used by SQL Agent
GO
USE [msdb];
GO
IF (SELECT host_platform FROM sys.dm_os_host_info) <> N'Windows' BEGIN PRINT N'  Not Windows - set the Agent mail profile with mssql-conf instead'; RETURN; END;
EXEC master.dbo.xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'UseDatabaseMail',
     N'REG_DWORD',
     1;
GO
IF (SELECT host_platform FROM sys.dm_os_host_info) <> N'Windows' BEGIN PRINT N'  Not Windows - set the Agent mail profile with mssql-conf instead'; RETURN; END;
DECLARE @Profile nvarchar(128) = (SELECT value FROM #InstallSettings WHERE name = N'MailProfile');
EXEC master.dbo.xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'DatabaseMailProfile',
     N'REG_SZ',
     @Profile;
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
     @email_save_in_sent_folder = 1;
GO

PRINT N'  Restart SQL Server Agent so it picks up the mail profile.';
GO