Sunday, March 3, 2013

SelfNote: PowerShell on SQL Jobs

You know what. The lack of "Start" button on Win8 makes me really learn PowerShell. I feel I am more like a Unix admin than an average user. :-D

 # disable backup job on a server  
 function Disable-BackupJob($serverName)  
 {  
   invoke-command -computerName $serverName -ScriptBlock { `  
     param($serverName); `  
     [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null; `  
     $srv = New-Object Microsoft.SqlServer.Management.SMO.Server($serverName); `  
     $jobs = $srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Where-Object { $_.Name -like '*backup*' }; `  
     foreach ($job in $jobs) `  
     { `  
       write-host "$serverName.$job is to FALSE"; `  
       $job.IsEnabled = $false; `  
       $jobs.Alter(); `  
     }; `  
     $jobs = $srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Where-Object { $_.Name -like '*backup*' }; `  
     if ($jobs.Count -eq 0) { write-host "$serverName Done." } else { write-host "$serverName failed!" }; `  
   }`  
   -ArgumentList $serverName  
 }  

No comments: