Scripting SQL Agent Jobs With Powershell

Question

Hi,

  I have a Sqlserver 2008 and about 120 Sql Agent jobs. We are planning to create new environments and I need to create the same jobs in all the new environments. Now i can script each job individually and then run them on the new servers but i was wondering if there is a Powershell way of looping though all the jobs and generate scripts for them and put them all in one big Sql, so i can just run that 1 script in any new environments that we add to create all the jobs that we have.

 

I found the below script online and am trying to make changes to it but cannot find the methods to scripts a existing object

 

# Load SMO extension

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

# Get List of sql servers to check

$sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";

 

# Loop through each sql server from sqlservers.txt

foreach($sqlserver in $sqlservers)

{

      # Create an SMO Server object

      $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;

 

      # Jobs counts

      $totalJobCount = $srv.JobServer.Jobs.Count;

      $failedCount = 0;

      $successCount = 0;

 

      # For each jobs on the server

      foreach($job in $srv.JobServer.Jobs)

      {

            # Default write colour

            $colour = "Green";

            $jobName = $job.Name;

            $jobEnabled = $job.IsEnabled;

            $jobLastRunOutcome = $job.LastRunOutcome;

 

            # Set write text to red for Failed jobs

            if($jobLastRunOutcome -eq "Failed")

            {

                  $colour = "Red";

                  $failedCount += 1;

            }

            elseif ($jobLastRunOutcome -eq "Succeeded")

            {

                  $successCount += 1;

            }

            Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";

 

      }

 

      # Writes a summary for each SQL server

      Write-Host -ForegroundColor red "=========================================================================================";

      Write-Host -ForegroundColor red "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount.";

      Write-Host -ForegroundColor red "=========================================================================================";

}

 

 

Any help greatly appreciated.

 

Thanks

Ashish

Answer 1

Save the following as a ps1 script file for example scriptJobs.ps1

param($sqlserver)

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

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

$srv.JobServer.Jobs | foreach {$_.Script()}

 

Then call the script passing the server instance name:

./scriptJobs.ps1 Z003\r2

Answer 2

Hi,

I want to scipt out all agent jobs excluding mirroring jobs. The jobs that contain the word mirroring have to be excluded. Also, i would like to parametrize the output file. Can you please let me know how to do this in Powershell

Thanks,

Kiran

Answer 3

A small modification to the original can accomplish your goal of excluding mirroring jobs:

Save the following as a ps1 script file for example scriptJobs.ps1

param($sqlserver)

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

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

$srv.JobServer.Jobs | where {$_.Name -notlike "*mirror*"} | foreach {$_.Script()}

 

Then call the script passing the server instance name:

./scriptJobs.ps1 Z003\r2

Answer 4

Thanks for the reply. We pass the instance name in the following format

 -S"KIRAN\SECONDARY"

they follow this format..so can you please let me know how to handle this in the script..

Thanks,

Kiran 

Answer 5

Thanks for the reply. We pass the instance name in the following format

 -S"KIRAN\SECONDARY"

they follow this format..so can you please let me know how to handle this in the script..

Thanks,

Kiran 

You can use:

./scriptJob.ps1 -S:"KIRAN\SECONDARY" or

./scriptJob.ps1 -S "KIRAN\SECONDARY"

Powershell does partial parameter name matching--the $sqlserver parameter can be shorten to just "S". PowerShell requires either a space or colon between the parameter name and parameter value.

Answer 6

Thanks for your reply. The output needs to be saved to a file. We have this Out-File cmdlet but i want the filepath to be a parameter....whatever value we pass to this parameter, the output should be saved in that location...is there any way to do this...

Thanks,

Kiran

Answer 7

Sure just add second parameter to the script as follows:

param($sqlserver,$path)

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

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

$srv.JobServer.Jobs | foreach {$_.Script()} | out-file -path $path

Answer 8

Thanks a lot for the reply. Is there anyway to handle -S"KIRAN\SECONDARY" format in the script. I know that you have told me to use

./scriptJob.ps1 -S:"KIRAN\SECONDARY" or

./scriptJob.ps1 -S "KIRAN\SECONDARY"

Actually, the OPS team passes parameters in the format -S"KIRAN\SECONDARY" and they are pretty specific about tht... so is there anyway to neglect the the first 2 characters in the string and by using some function in the script

 

Thanks,

Kiran

Answer 9

If you replace the named parameters i.e. remove this line

param($sqlserver,$path)

with the built-in $args variable you could workaround this:

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

$sqlserver = $args[0].Replace("-s","") -replace '"'

$path = $args[1].Replace("-p","") -replace '"'

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

$srv.JobServer.Jobs | foreach {$_.Script()} | out-file -path $path

 

Then call the script with

./scriptjob.ps1 -s"myserver" -p"c:\path\script.sql"

Answer 10

You can also use the ready to run application which creates also calling .sql files to apply the jobs to the server.

http://blogs.msdn.com/b/jenss/archive/2009/01/27/script-out-jobs-in-sql-server.aspx

-jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

Answer 11

I have enhanced this for my own purpose of writing each job to it's own text file.  I needed that to use with version control. 

param($sqlserver)

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

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

$jobs = $srv.JobServer.Jobs


ForEach ( $job in $jobs )
	{
		$jobname = $job.Name + ".sql"
		$job.Script | Out-File $jobname
	}

Thanks for the start cmille19

Further testing revealed this created all the files I expect but they are filled with

MemberType          : Method
OverloadDefinitions : {System.Collections.Specialized.StringCollection Script(), System.Co
                      llections.Specialized.StringCollection Script(Microsoft.SqlServer.Ma
                      nagement.Smo.ScriptingOptions scriptingOptions)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : System.Collections.Specialized.StringCollection Script(), System.Col
                      lections.Specialized.StringCollection Script(Microsoft.SqlServer.Man
                      agement.Smo.ScriptingOptions scriptingOptions)
Name                : Script
IsInstance          : True

and not the SQL I expected, a little more work needed.

Answer 12

Found the issue, missing () after script. Should read

param($sqlserver)

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

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

$jobs = $srv.JobServer.Jobs


ForEach ( $job in $jobs )
	{
		$jobname = $job.Name + ".Job.sql"
		$job.Script() | Out-File $jobname
	}

Answer 13

  use
-Filepath instead of -path

param($sqlserver,$path)


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

$sqlserver = $args[0].Replace("-s","") -replace '"'

$path = $args[1].Replace("-p","") -replace '"'

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

$srv.JobServer.Jobs | foreach {$_.Script()} | out-file -Filepath $path

Answer 14

Save the script in test folder as ps1 and send the instance name as the parameter.It works thanks !!!

param($sqlserver)

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

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

$jobs = $srv.JobServer.Jobs

ForEach ( $job in $jobs )
 {
  $jobname = "c:\test\" +  $job.Name.replace(" ","_").replace("\","_").replace("[","_").replace("]","_").replace(".","_").replace(":","_") + ".sql"
  $job.Script() | Out-File $jobname
 }