Tuesday, April 24, 2012

How to script the SQL Server Agent Operators?


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;

3 comments:

  1. Thanks for creating and sharing!

    -- Missing NOT
    if exists (select * from dbo.sysoperators where name ='

    ReplyDelete
  2. Thank you bunch! this works like magic and saves me tons of time!!

    ReplyDelete
  3. Just what I was looking for! Thank you so much!

    ReplyDelete