As part of the Disaster recovery procedures, I wanted to script out every server object that we had created. This included SQL Server jobs, logins, operators, linked servers and proxies. I was able to script out everything but the SQL Server Agent operators.
After several unsuccessfull Google searches my colleague found the following script to do this.
USE msdb
set nocount on;
create table #tbl (
id int not null,
name sysname not null,
enabled tinyint not null,
email_address nvarchar(100) null,
last_email_date int not null,
last_email_time int not null,
pager_address nvarchar(100) null,
last_pager_date int not null,
last_pager_time int not null,
weekday_pager_start_time int not null,
weekday_pager_end_time int not null,
Saturday_pager_start_time int not null,
Saturday_pager_end_time int not null,
Sunday_pager_start_time int not null,
Sunday_pager_end_time int not null,
pager_days tinyint not null,
netsend_address nvarchar(100) null,
last_netsend_date int not null,
last_netsend_time int not null,
category_name sysname null);
insert into #tbl
EXEC sp_help_operator;
select 'USE msdb;' + char(13) + char(10) + 'if exists (select * from dbo.sysoperators where name =' + quotename(name, char(39)) + ') ' + char(13) + char(10) +
'exec sp_add_operator ' +
'@name = ' + quotename(name, char(39)) + ', ' +
'@enabled = ' + cast (enabled as char(1)) + ', ' +
'@email_address = ' + quotename(email_address, char(39)) + ', ' +
case
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end +
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' +
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) +
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39))
else ''
end +
case
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name
else ''
end +
'; ' + char(13) + char(10) + 'go'
from #tbl order by id;
drop table #tbl;
