Script Alerts

I know that I can script all of the server alerts,
operators, and jobs using enterprise manager, however,
does anyone know how I can go about scripting out the
alerts, operators, and jobs not using enterprise
manager. What I want to do is make this part of my
disaster recovery process to have the SQLAgent run a job
every night to capture a script of the alerts, operators,
and jobs so that I can use these scripts to recovery said
objects in case of a disaster.Take a look at the Script method in the SQL-DMO
documenation in the SQL Server Books Online.
Linchi
>--Original Message--
>I know that I can script all of the server alerts,
>operators, and jobs using enterprise manager, however,
>does anyone know how I can go about scripting out the
>alerts, operators, and jobs not using enterprise
>manager. What I want to do is make this part of my
>disaster recovery process to have the SQLAgent run a job
>every night to capture a script of the alerts, operators,
>and jobs so that I can use these scripts to recovery said
>objects in case of a disaster.
>.
>|||In addition to Linchi's reply, if you are not comfortable with SQL-DMO, you
can contact me and I would develop a script for you if you were to
compensate me for my time. Pls remove NO_SPAM(s) from my email address if
you do.
--
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
> I know that I can script all of the server alerts,
> operators, and jobs using enterprise manager, however,
> does anyone know how I can go about scripting out the
> alerts, operators, and jobs not using enterprise
> manager. What I want to do is make this part of my
> disaster recovery process to have the SQLAgent run a job
> every night to capture a script of the alerts, operators,
> and jobs so that I can use these scripts to recovery said
> objects in case of a disaster.
>|||But the script is rather simple to write. Here's a Perl
script I just whipped up to script out Alerts, Jobs, and
Operators:
# Begin script
use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO';
my $serverName = 'yourSQLServer';
my ($server, $obj);
my $script = shift; # get command line argument
$server = Win32::OLE->new('SQLDMO.SQLServer')
or die "Could not create SQLDMO object.";
$server->{LoginSecure} = 1;
$server->connect($serverName, '', '');
if (Win32::OLE->LastError()) {
die "***Err: Could not connect to $serverName.";
}
print "**** Scripting Alerts ... \n";
my $alerts = $server->JobServer->Alerts();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the alerts collection in
$serverName.";
}
foreach $obj (in($alerts)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
print "**** Scripting Jobs ... \n";
my $jobs = $server->JobServer->Jobs();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the jobs collection in
$serverName.";
}
foreach $obj (in($jobs)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
print "**** Scripting Operators ... \n";
my $operators = $server->JobServer->Operators();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the operators collection in
$serverName.";
}
foreach $obj (in($operators)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
$server->disconnect();
$server->DESTROY();
# End script
Linchi
>--Original Message--
>In addition to Linchi's reply, if you are not comfortable
with SQL-DMO, you
>can contact me and I would develop a script for you if
you were to
>compensate me for my time. Pls remove NO_SPAM(s) from my
email address if
>you do.
>--
>Ata R
>Parvan Consulting Inc
>NO_SPAMar_alias001@.NO_SPAMparvan.net
>
>"Z" <anonymous@.discussions.microsoft.com> wrote in message
>news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
>> I know that I can script all of the server alerts,
>> operators, and jobs using enterprise manager, however,
>> does anyone know how I can go about scripting out the
>> alerts, operators, and jobs not using enterprise
>> manager. What I want to do is make this part of my
>> disaster recovery process to have the SQLAgent run a job
>> every night to capture a script of the alerts,
operators,
>> and jobs so that I can use these scripts to recovery
said
>> objects in case of a disaster.
>
>.
>|||AndI have a VB version of this that will script out the whole server for
you. As Linchi alludes to, SQL DMO is an untuitive object model and can
really help you out.
If you want the application then mail me
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:00c901c3d53f$db652240$a301280a@.phx.gbl...
> But the script is rather simple to write. Here's a Perl
> script I just whipped up to script out Alerts, Jobs, and
> Operators:
> # Begin script
> use strict;
> use Win32::OLE 'in';
> use Win32::OLE::Const 'Microsoft SQLDMO';
> my $serverName = 'yourSQLServer';
> my ($server, $obj);
> my $script = shift; # get command line argument
> $server = Win32::OLE->new('SQLDMO.SQLServer')
> or die "Could not create SQLDMO object.";
> $server->{LoginSecure} = 1;
> $server->connect($serverName, '', '');
> if (Win32::OLE->LastError()) {
> die "***Err: Could not connect to $serverName.";
> }
> print "**** Scripting Alerts ... \n";
> my $alerts = $server->JobServer->Alerts();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the alerts collection in
> $serverName.";
> }
> foreach $obj (in($alerts)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> print "**** Scripting Jobs ... \n";
> my $jobs = $server->JobServer->Jobs();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the jobs collection in
> $serverName.";
> }
> foreach $obj (in($jobs)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> print "**** Scripting Operators ... \n";
> my $operators = $server->JobServer->Operators();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the operators collection in
> $serverName.";
> }
> foreach $obj (in($operators)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> $server->disconnect();
> $server->DESTROY();
> # End script
> Linchi
> >--Original Message--
> >In addition to Linchi's reply, if you are not comfortable
> with SQL-DMO, you
> >can contact me and I would develop a script for you if
> you were to
> >compensate me for my time. Pls remove NO_SPAM(s) from my
> email address if
> >you do.
> >
> >--
> >Ata R
> >Parvan Consulting Inc
> >NO_SPAMar_alias001@.NO_SPAMparvan.net
> >
> >
> >"Z" <anonymous@.discussions.microsoft.com> wrote in message
> >news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
> >> I know that I can script all of the server alerts,
> >> operators, and jobs using enterprise manager, however,
> >> does anyone know how I can go about scripting out the
> >> alerts, operators, and jobs not using enterprise
> >> manager. What I want to do is make this part of my
> >> disaster recovery process to have the SQLAgent run a job
> >> every night to capture a script of the alerts,
> operators,
> >> and jobs so that I can use these scripts to recovery
> said
> >> objects in case of a disaster.
> >>
> >
> >
> >.
> >