locked
Scripting SQL Agent Jobs With Powershell RRS feed

  • 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

    Thursday, May 27, 2010 8:11 PM

Answers

  • 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

    • Marked as answer by KJian_ Thursday, June 3, 2010 3:36 AM
    Saturday, May 29, 2010 3:03 PM

All replies

  • 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

    • Marked as answer by KJian_ Thursday, June 3, 2010 3:36 AM
    Saturday, May 29, 2010 3:03 PM
  • 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

    Thursday, July 22, 2010 6:17 PM
  • 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

    Thursday, July 22, 2010 6:32 PM
  • 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 

    Thursday, July 22, 2010 6:36 PM
  • 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.

    Thursday, July 22, 2010 7:26 PM
  • 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

    Thursday, July 22, 2010 7:44 PM
  • 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

    • Proposed as answer by Kiran Tech Thursday, July 22, 2010 9:18 PM
    Thursday, July 22, 2010 7:51 PM
  • 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

    Thursday, July 22, 2010 9:22 PM
  • 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"

    Friday, July 23, 2010 1:14 AM
  • 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
    Friday, July 23, 2010 10:43 AM
  • 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.

    Wednesday, September 15, 2010 3:22 PM
  • 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
    	}
    
    • Proposed as answer by Darren Comeau Thursday, September 16, 2010 2:11 PM
    Thursday, September 16, 2010 2:10 PM
  •   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
    Friday, January 6, 2012 2:23 PM
  • 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
     }

    • Proposed as answer by Premjit_24 Wednesday, April 11, 2012 7:10 AM
    Wednesday, April 11, 2012 7:09 AM