Scripting & automating all SQL Server Jobs

• imransi17

  Mr or Mrs. 500

  Points: 531

  More actions

  May 22, 2013 at 1:55 pm

  #273020

  Hello,

  I am looking to script out all the SQL Server agent jobs and automate it, for high availability purposes on StandBy Server.

  here is the script that I found.... http://www.nilkanth.com/2004/07/18/sql-jobs-auto-backup/

  but it does NOT return any value...hmmm?

  Please keep in mind that I am using SQL 2008R2 Enterprise version.

  Please help !

  Thanks

• saran.dharsh

  Right there with Babe

  Points: 723

  More actions

  May 22, 2013 at 8:46 pm

  #1617666

  Hi ,

  Are You enabled Powershell?

• saran.dharsh

  Right there with Babe

  Points: 723

  More actions

  May 23, 2013 at 4:05 am

  #1617782

  Below script helps to script sql Jobs

  param($sqlserver)

  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

  $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

  $jobs = $srv.JobServer.Jobs

  /*

  #Scripting execept Maintenance & Logshipping Jobs

  $jobs = $srv.JobServer.Jobs | Where-Object {$_.category -notlike "*repl*" -and $_.category -notlike "*shipping*"

  -and $_.category -notlike "*Maintenance*" }

  */

  #Build this portion of the directory structure out here in case scripting takes more than one minute.

  $DateFolder = get-date -format yyyyMMddHHmm

  $SavePath = "E:\TESTING\DBSCRIPT\Script\" + $DateFolder

  ForEach ( $job in $jobs )

  {

  #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name

  $scriptr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

  $jobname = $job.Name.replace(" ","_").replace("\","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_").replace("*","_")

  #This section builds folder structures. Remove the date folder if you want to overwrite.

  if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")

  {"Scripting Out $TypeFolder $ScriptThis"}

  else {new-item -type directory -name "$TypeFolder" -path "$SavePath"}

  $jobname = "$SavePath\$TypeFolder\$job.SQL"

  $job.Script() | Out-File $jobname

  }

  Save the Script in PS1 format

  Scheduling Format : powershell "& E:\TESTING\DBSCRIPT\Script\Weekly_JobScript.PS1 'Server1\Instance' "