Archive

Posts Tagged ‘sql+server’

Using PowerShell to Kick off SQL Server Agent Jobs

We’re in the process of a few upgrades around our office and one of those will require a full shutdown mid-business day. To expedite the shutdown, I wanted a quick way to run our backup jobs (hot backups), move them, and down the boxes.

Remember, I’m lazy–walking around our server room smacking rack switches takes effort. I want to sit and drink coffee while the world comes to a halt.

Here’s a bit of what I came up with. The script handles four things:

  • accepts a server name parameter (so I can iterate through a list of servers),
  • checks to see if the server is online (simple ping),
  • checks to see if it’s a SQL Server (looks for the MSSQLSERVER service),
  • iterates through the jobs and, if the -run switch is passed, kicks them off.

As with most, the couple of helper functions are more than the actual script itself.

param (
  [string]$server = $(Read-Host -prompt "Enter the server name"),
  [switch]$run = $false
)

# little helper function to ping the remove server.
function get-server-online ([string] $server) {
  $serverStatus = 
    (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
  
  Write-Host "Server Online: $serverStatus"
  return $serverStatus
}

# little helper function to check to see if the remote 
# server has SQL Server running.
function is-sql-server([string] $server) {
   if (get-server-online($server)) {
    
    $sqlStatus = (gwmi Win32_service -computername $server | 
        ? { $_.name -like "MSSQLSERVER" }).Status -eq "OK"
        
    Write-Host "SQL Server Found: $sqlStatus"
    return $sqlStatus
   }
}

# uhh, because someday I may need to add more to this?
function cannot-find-server([string] $server) {
  Write-Host "Cannot contact $server..."
}

if (is-sql-server($server)) {
  
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
    $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server
    $jobs = $srv.JobServer.Jobs | ? {$_.IsEnabled -eq $true} 

    $jobs | 
        select Name, CurrentRunStatus, LastRunOutcome, LastRunDate, NextRunDate | 
        ft -a
    
    if ($run) {
      $jobs | % { $_.Start(); "Starting job: $_ ..." }
      
    }
}
else {
  cannot-find-server($server)
}

Things left to do–someday…?

  • Allow picking and choosing what jobs get kicked off…

It’s not fancy, but it’ll save time backing up and shutting down a couple dozen SQL instances and ensuring they’re not missed in the chaos. 🙂