This script will create an operator and standard alerts so SQL Server tells you about serious problems as they happen, rather than when a user does. It creates the DBA_Alerts operator and 13 standard alerts: corruption errors 823, 824 and 825, severity 17 to 25, and database option changes (5084), all emailing the operator. Change [email protected] at the top to your own address before running it. Tested on SQL Server 2017 and 2025 and safe to run more than once.

Before you create the operator and standard alerts

The alerts send email, so Database Mail must be set up and the Agent mail profile enabled first (see Setup_SQL_Server_Agent_Alerting_System). If a corruption alert fires, follow Database Suspect, Recovery Pending or Stuck Recovering and run a database integrity check. Microsoft’s reference is creating an alert using severity level.

The operator and standard alerts script

-- 07.Create_Operator_and_Standard_Alerts.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'DBAEmail',              N'[email protected]');             -- who gets alert and job-failure emails
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysoperators]
    WHERE name = 'DBA_Alerts'
) IS NULL
    BEGIN
        DECLARE @DBAEmail nvarchar(400) = (SELECT value FROM #InstallSettings WHERE name = N'DBAEmail');
        EXEC msdb.dbo.sp_add_operator
             @name = N'DBA_Alerts',
             @enabled = 1,
             @pager_days = 0,
             @email_address = @DBAEmail;
END;
    ELSE
    BEGIN
        PRINT 'Operator Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Corruption Alert 823 Error'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Corruption Alert 823 Error',
             @message_id = 823,
             @severity = 0,
             @enabled = 1,
             @delay_between_responses = 180,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Corruption Alert 823 Error',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Corruption Alert 823 Error] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Corruption Alert 824 Error'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Corruption Alert 824 Error',
             @message_id = 824,
             @severity = 0,
             @enabled = 1,
             @delay_between_responses = 180,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Corruption Alert 824 Error',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Corruption Alert 824 Error] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Corruption Alert 825 Error'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Corruption Alert 825 Error',
             @message_id = 825,
             @severity = 0,
             @enabled = 1,
             @delay_between_responses = 180,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Corruption Alert 825 Error',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Corruption Alert 825 Error] Already Exists';
END;
GO

USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 17'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 17',
             @message_id = 0,
             @severity = 17,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 17',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 17] Already Exists';
END;
GO

USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 18'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 18',
             @message_id = 0,
             @severity = 18,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 18',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 18] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 19'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 19',
             @message_id = 0,
             @severity = 19,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 19',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 19] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 20'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 20',
             @message_id = 0,
             @severity = 20,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 20',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 20] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 21'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 21',
             @message_id = 0,
             @severity = 21,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 21',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 21] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 22'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 22',
             @message_id = 0,
             @severity = 22,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 22',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 22] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 23'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 23',
             @message_id = 0,
             @severity = 23,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 23',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 23] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 24'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 24',
             @message_id = 0,
             @severity = 24,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 24',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 24] Already Exists';
END;
GO
USE [msdb];
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Severity Alert 25'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Severity Alert 25',
             @message_id = 0,
             @severity = 25,
             @enabled = 1,
             @delay_between_responses = 120,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Severity Alert 25',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Severity Alert 25] Already Exists';
END;
GO
IF
(
    SELECT 1
    FROM [dbo].[sysalerts]
    WHERE name = 'Database Option Change'
) IS NULL
    BEGIN
        EXEC msdb.dbo.sp_add_alert
             @name = N'Database Option Change',
             @message_id = 5084,
             @severity = 0,
             @enabled = 1,
             @delay_between_responses = 0,
             @include_event_description_in = 1;
        EXEC msdb.dbo.sp_add_notification
             @alert_name = N'Database Option Change',
             @operator_name = N'DBA_Alerts',
             @notification_method = 1;
END;
    ELSE
    BEGIN
        PRINT 'Alert [Database Option Change] Already Exists';
END;
GO
GO

From my SQL Server DBA handbook.