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
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.
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'