USE [msdb];
GO
IF
(
SELECT 1
FROM [dbo].[sysoperators]
WHERE name = ‘DBA_Alerts’
) IS NULL
BEGIN
EXEC msdb.dbo.sp_add_operator @name = N’DBA_Alerts’, @enabled = 1, @pager_days = 0, @email_address = N’DBA_Alerts@dwf.co.uk’;
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 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