powershell to script alerts/operators

Question

powershell to script alerts/operators

goher2000

SSCertifiable

Points: 5479
More actions
September 14, 2018 at 8:25 am

#368181

I am looking for powershell script to script out alters and operators, can someone help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

goher2000 - Friday, September 14, 2018 8:25 AM
I am looking for powershell script to script out alters and operators, can someone help

Alters to what?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michael L John One Orange Chip Points: 27056 More actions · Answer 2

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$a = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions $DateTime = (Get-Date).ToString("yyyyMMdd-HHmmss") $instanceName = "$(ESCAPE_SQUOTE(SRVR))"; $instanceName = $instanceName.Replace("\","$") $directory_location = "H:\SQL_Backups" + $instanceName + "\Objects" new-item $directory_location -itemtype directory $a.filename = $directory_location + "\_" + $DateTime + "_Operators.sql"

$a.AppendToFile = $True $a.ToFileOnly = $true $a.ScriptBatchTerminator = $true

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $(ESCAPE_SQUOTE(SRVR)) $srv.JobServer.Operators | foreach {$_.Script($a)}

This is for operators. I do not script out alerts, they are the same set on every servers.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQLPirate SSCrazy Points: 2880 More actions · Answer 3

goher2000 - Friday, September 14, 2018 8:25 AM
I am looking for powershell script to script out alters and operators, can someone help

DBATools (https://dbatools.io/) makes this really simple and will generate valid sp_add* scripts for you.

$server = 'ServerName' #specify your target server $filename = 'C:\outputfolder\{0}_export.sql' -f ($server) #change your folder and name your output file

get-dbaagentalert -sqlserver $server | export-dbascript -path $filename -append get-dbaagentoperator -sqlserver $server | export-dbascript -path $filename -append

It's also a great toolset in general for SQL Server operations and administration.

Michael L John One Orange Chip Points: 27056 More actions · Answer 4

SQLPirate - Friday, September 14, 2018 12:20 PM
goher2000 - Friday, September 14, 2018 8:25 AM
I am looking for powershell script to script out alters and operators, can someone help
DBATools (https://dbatools.io/) makes this really simple and will generate valid sp_add* scripts for you.
$server = 'ServerName' #specify your target server $filename = 'C:\outputfolder\{0}_export.sql' -f ($server) #change your folder and name your output file
get-dbaagentalert -sqlserver $server | export-dbascript -path $filename -append get-dbaagentoperator -sqlserver $server | export-dbascript -path $filename -append
It's also a great toolset in general for SQL Server operations and administration.

Which is actually what I am in the process of switching to!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQLPirate SSCrazy Points: 2880 More actions · Answer 5

Michael L John - Friday, September 14, 2018 12:58 PM
Which is actually what I am in the process of switching to!

I can't say enough good things about it. It's easily the most handy toolset I've adopted for tasks of this nature.

goher2000 SSCertifiable Points: 5479 More actions · Answer 6

goher2000

SSCertifiable

Points: 5479

September 17, 2018 at 9:09 am

#2005962

Thanks Guys