Adding a SQL Server event alert

This recipe walks you through the steps in adding a SQL Server event alert.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module and create a new SMO Server object, as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

3. Add the following script and run:

   $jobserver = $server.JobServer
   #for purposes of our exercise, we will drop this 
   #alert if it already exists
   $alertname = "Test Alert"
   $alert = $jobserver.Alerts[$alertname]
   
   if($alert)
   {
     $alert.Drop()
   }
   
   #accepts a JobServer and an alert name
   $alert  = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert $jobserver, "Test Alert"
   $alert.Severity = 10
   #Raise Alert when Message contains
   $alert.EventDescriptionKeyword = "failed...