Using powershell to run SQL Jobs serially

We had some backups fail due to disk space issues.  Once we cleared the space, we needed to run the backups as I didn’t want to wait an entire day.  I didn’t want to run them at once due to contention issues and I am way too lazy to manually start each one and wait for it to finish.

Powershell to the rescue. 

The SQL provider is slick, gives you a sql server psdrive that you can explore.  Finding the jobs was easy enough.  Finding failed jobs was easy enough.  Running the jobs… wtf?  Not hard, but seems like I should be able to just start a job rather than send it as a sql command.  I hardly need the sql snapin for that.

Anyway, update SERVERNAME (and potentially instance) in the code below and it will find all your failed jobs and run them serially.

Sweet

cd SQLSERVER:\SQL\SERVERNAME\DEFAULT\JobServer\Jobs

$jobs  = dir | where {$_.lastrunoutcome -eq "failed" }

foreach ($job in $jobs) {

      "starting $($job.name)"

      invoke-sqlcmd -database msdb "sp_start_job '$($job.name)'"

      while ((invoke-sqlcmd -database msdb "sp_help_jobactivity @job_id = NULL, @job_name =  '$($job.name)'").run_status -ne 1) {

            "still running..."

            sleep 120

      }

      "Completed at $(date)"

}