PowerShell Script to Monitor the Status of a SQL Job

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$job = $server.JobServer.Jobs["Test Job"]
$now = Get-Date
do
{
 Start-Sleep -Seconds 1
 $job | select Name,CurrentRunStatus,LastRunDate
 $job.Refresh()
}
while($job.LastRunDate -lt $now)
$job | select Name,CurrentRunStatus,LastRunDate

PowerShell Scripts to Continuously Ping a Host and Give an Audible Alert

#Used to continuously ping a host and get an audible alert when it is back up.

function ContinuousPing-Beep
{
    param([string]$computer)
    $ping = new-object System.Net.NetworkInformation.Ping
    $result = $ping.Send($computer);
    if($result.Status -eq "Success")
    {
        Write-Host "Reply received from $computer" -Foreground green
        Write-Host `a;
    }
    else
    {
        do{$result = $ping.Send($computer);Write-Host "Reply from $computer. Destionation host unreachable." -Foreground red}
            until($result.Status -eq "Success")
            Write-Host "Reply received from $computer" -Foreground green
            Write-Host `a;
    }
}

function ContinuousPing-Voice
{
    param([string]$computer)
    
    $voice = new-object -com SAPI.SpVoice
    $ping = new-object System.Net.NetworkInformation.Ping
    $result = $ping.Send($computer);
    if($result.Status -eq "Success")
    {
        $voice.Speak("Reply received from $computer", 1)
    }
    else
    {
        do{$result = $ping.Send($computer);Write-Host "Reply from $computer. Destionation host unreachable." -Foreground red}
          until($result.Status -eq "Success")
       
        $voice.Speak("Reply received from $computer", 1)

    }
}

ContinuousPing-Beep "192.168.1.1"

ContinuousPing-Voice "192.168.1.1"

Simulating Log Shipping with Windows PowerShell

Over the past few months I have been writing the high availability and desaster recovery documentation and procedures. When I first took this position we had no sort of disaster recovery plan in place. We used tape backups and SQL Server backups that were only done once a day. T-SQL log backups were done only once during the day and there was nothing set up to notify the database administrator (that is me) if one of the jobs failed. I also had nothing to restore the backup to, if there was a hardware failure until the replacement equipment was received. Add to the fact that the SQL Server itself is out of any sort of warranty and we had a potentially nast situation if something did go wrong.

In addition to changing the backup plans so that we are doing t-log backups every hour and adding alerts, operators, and notifications tot he server I started kicking around teh idea of using transaction log shipping to add an extra layer of protection to the system. But the issue was the production database server is SQL Server 2000 Standard, which does not include log shipping.

I decided to write a PowerShell script that would use the backups I was alreeady creating to simulate log shipping to a warm standby server. Here is what I have so far.