Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Script out SQL agent jobs from powershell

Posted by rahmanagoro on August 26, 2010


At my shop, we currently use SVN to store database objects, one object type which is often overlooked is SQL agent jobs, I needed a way in which I can generate sql agent jobs into sql files, SQL management studio allows you to script jobs, but you can either do this one at a time, or script all the jobs in one big file. The two options are not really ideal for me, the reason being that I may choose to be selective about which jobs I install on a server, other jobs are installed as part of an installation suite, e.g management data warehouse in SQL 2008.

I have written a simple powershell script that will simple script out all SQL agent jobs, one might have multiple servers, I have made the script such that it reads a text file for a list of servers, simply save this file on a location and call the powershell script, you also need to specify where you want the script to be generated to as well.


#Original
# 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";
$sqlservers = Get-Content "C:\Servers.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;
 			$jobNameFile = "C:\SQLJOBS\" + $jobName+".sql"

 			Write-Host $job.Name
			Write-Host "The location of the file is called " $jobNameFile

#			[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
			$job | foreach {$_.Script()} | out-file $jobNameFile

            # 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 "=========================================================================================";
}
Advertisements

4 Responses to “Script out SQL agent jobs from powershell”

  1. Vishal Desai said

    I have some jobs running on iftstdb03 but its not reported.

    PS C:\CheckSQLServer> ./test2.ps1

    SERVER = iftstdb03 JOB = ENABLED = LASTRUN =
    ================================================================================
    =========
    iftstdb03 total jobs = , success count 0, failed jobs = 0.
    ================================================================================
    =========
    BackupDatabases.Subplan_1
    SERVER = ifprddb03 JOB = BackupDatabases.Subplan_1 ENABLED = True LASTRUN = Succ
    eeded
    BackupLogs.Subplan_1
    SERVER = ifprddb03 JOB = BackupLogs.Subplan_1 ENABLED = True LASTRUN = Succeeded

    syspolicy_purge_history
    SERVER = ifprddb03 JOB = syspolicy_purge_history ENABLED = True LASTRUN = Succee
    ded
    ================================================================================
    =========
    ifprddb03 total jobs = 3, success count 3, failed jobs = 0.
    ================================================================================

  2. I have now checked it, the script relies on a path to save the file for the jobs, in the case of the script C:\SQLJOBS was hardcoded into the script. In the next release I will allow this to be passed as a parameter. If you create a folder called SQLJOBS under C:\ then it should work. I also changed the script such that it geenrates .sql scripts.

  3. Vishal Desai said

    I have commented out hard coded part. I have same job running on iftstdb03 and ifprddb03 but one under iftstdb03 are not reported.

    PS C:\CheckSQLServer> ./test2.ps1
    xyz [iftstdb03].JobServer.Jobs

    SERVER = iftstdb03 JOB = ENABLED = LASTRUN =
    ==============================================================================
    iftstdb03 total jobs = , success count 0, failed jobs = 0.
    ==============================================================================
    xyz 3 [ifprddb03].JobServer.Jobs
    BackupDatabases.Subplan_1
    SERVER = ifprddb03 JOB = BackupDatabases.Subplan_1 ENABLED = True LASTRUN = Succ
    eeded
    BackupLogs.Subplan_1
    SERVER = ifprddb03 JOB = BackupLogs.Subplan_1 ENABLED = True LASTRUN = Succeeded

    ==============================================================================
    ifprddb03 total jobs = 3, success count 3, failed jobs = 0.
    ==============================================================================

    #Original
    # 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”;
    $sqlservers = Get-Content “C:\CheckSQLServer\servers1.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;
    Write-Host -ForegroundColor Red “xyz $totalJobCount $srv.JobServer.Jobs”;
    # 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;
    # $jobNameFile = “C:\” + $jobName+”.txt”
    Write-Host $job.Name
    # Write-Host “The location of the file is called ” $jobNameFile
    # [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
    # $job | foreach {$_.Script()} | out-file $jobNameFile
    # 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 “=========================================================================================”;
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: