On the first day of Christmas dbatools gave to me: Find-DbaCommand
On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer
On the third day of Christmas dbatools gave to me: DbaMaxMemory
On the fourth day of Christmas dbatools gave to me: DbaAgentJob
On the fifth day of Christmas dbatools gave to me: DbaLogin
On the sixth day of Christmas dbatools gave to me: DbaTempDbConfig
On the seventh day of Christmas dbatools gave to me: DbaAgentAlert
Today we will talk about a not so popular topic: SQL Agent Alerts. Normally this is something you can set and forget when building your servers. However, there might be occasions where you need to create make some modifications to current alerts or just get a list of what alerts you have set across your environment. Or lets say you have your alerts dialed in perfectly on one server, bam Copy-DbaAgentAlert to the rescue. Now all your servers have those perfect alerts that won’t wake you up in the middle of the night.
By default, all alerts are copied. The -Alert parameter is auto-populated for command-line completion and can be used to copy only specific alerts.
If the alert already exists on the destination, it will be skipped unless -Force is used.
This function returns SQL Agent alerts.
Set-DbaAgentAlert updates an alert in the SQL Server Agent with parameters supplied.
Get-DbaAgentAlert -SqlInstance localhost\sql2017 | Out-GridView
Advanced Command Usage:
Now I have an occurrence where our alerts do not always work in our development environments. No e-mail access and some other things. So when we provision new servers we put the alerts and they are enabled. So I put this together using the PowerShell pipeline function which is supported by almost all dbatools commands. I get the alerts I am interested in then I pipe that over to the set command to disable them.
Get-DbaAgentAlert -SqlInstance localhost\sql2017 | Where-Object IsEnabled -EQ $true | Set-DbaAgentAlert -Disabled
Voila, now are alerts are disabled!!