How to script alerts in SQL Server?

Posted on

Question :

after trying out this script: Script out all alerts By Carolyn Richardson

or even the traditional way of ssms -> right click -> script alert as

none of these methods are fully comprehensive because they miss out information that is inside the alert, like the job it calls, etc.

for example I have been Setting Up Failover Alerts

and I get many actions set up as you can see on the script below:

-- 1480 - AG Role Change (failover)
EXEC msdb.dbo.sp_add_alert
        @name = N'AG Role Change',
        @message_id = 1480,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification 
        @alert_name = N'AG Role Change', 
        @operator_name = N'<OperatorName,sysname,Alerts>', 
        @notification_method = <Notification,INT,1>; 
GO

enter image description here

is there a way to script all these info along with the alert?

Answer :

I was able to get all the properties scripted out.

  • Open SSMS
  • Right click on the job
  • Click properties
  • Use script button on the top

I created a test job with many features.

enter image description here
enter image description here
enter image description here

This is what I get when I scripted out.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocked Tasks > 5', 
        @message_id=0, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=183, 
        @include_event_description_in=3, 
        @database_name=N'', 
        @notification_message=N'This is a test', 
        @event_description_keyword=N'', 
        @performance_condition=N'Workload Group Stats|Blocked tasks|default|>|5', 
        @wmi_namespace=N'', 
        @wmi_query=N'', 
        @job_id=N'b97f9d4d-a22d-4745-9d19-9fadd168b4b1'
GO
EXEC msdb.dbo.sp_update_notification 
      @alert_name=N'Blocked Tasks > 5', 
      @operator_name=N'sysadmin', 
      @notification_method = 1
GO

I ran an extended event trace to see what get called. I changed the select statement to select * to see all the values.

EXEC Sp_executesql 
N'  create table #tmp_sp_help_alert  (id int null, name nvarchar(128) null,  event_source nvarchar(100) null, event_category_id int null, event_id int null, message_id int null,  severity int null, enabled tinyint null, delay_between_responses int null, last_occurrence_date int null, last_occurrence_time int null, last_response_date int null, last_response_time int null, notification_message nvarchar(512) null, include_event_description tinyint null, database_name nvarchar(128) null, event_description_keyword nvarchar(100) null, occurrence_count int null, count_reset_date int null, count_reset_time int null, job_id uniqueidentifier null, job_name nvarchar(128) null, has_notification int null, flags int null, performance_condition nvarchar(512) null, category_name nvarchar(128) null,  wmi_namespace nvarchar(max) null,  wmi_query nvarchar(max) null, type int null)   insert into #tmp_sp_help_alert exec msdb.dbo.sp_help_alert         



SELECT * FROM #tmp_sp_help_alert AS tsha WHERE (tsha.name=@_msparam_0)   drop 
  table #tmp_sp_help_alert', 
    N'@_msparam_0 nvarchar(4000)', 
    @_msparam_0=N'Blocked Tasks > 5' 

Leave a Reply

Your email address will not be published. Required fields are marked *