Using SMO With PowerShell To Obtain SQL Server Job Information

Problem

Scripting SQL Server Agent Jobs is useful for backups, disaster recovery or saving revisions. There are many ways SQL Server Agent Job scripts can be generated and in this tip we will look at the SMO library with PowerShell.

Solution

When executing the below functions, remember that the SMO library may be in a different folder, depending on the version of SQL Server (the below scripts are in SQL Server 2014’s SDK folder). For instance, the location of the SMO library for my 2008R2 folder is in C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll.

PowerShell Script to Generate SQL Server Job Script

The below function will script a job (or jobs) by name and we pass in the server name, the SMO library location (stored in the $smo variable) and the name of the name, which we can keep null (blank) if we want all jobs, and the path where we want the jobs saved.

For environments which have more than one server, I would recommend a derivative of the second script, but this script will work if you need to quickly script one job to one server.

Function Script-Jobs { 
    Param(
    [string]$server
    , [string]$smolibrary
    , [string]$name
    , [string]$path
    )
    Process
    {
        $nl = [Environment]::NewLine
        Add-Type -Path $smolibrary
        $jobserver = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
        foreach ($job in $jobserver.JobServer.Jobs | Where-Object {$_.Name -like "$name*"})
        {
            $newfile = $path + $job.Name + ".sql"
            New-Item $newfile -ItemType file | Out-Null
            "USE msdb" + $nl + "GO" + $nl + $nl + $job.Script() | Out-file $newfile
        }
    }
}
###  Note that the SMO library may be in a different directory, depending on your version of SQL Server
$smo = "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Script-Jobs -server "OURSERVER\OURINSTANCE" -smolibrary $smo -name "" -path "C:\OurFolder\"

The output is T-SQL, so we can save the output as a variable and then run it against another server, using Invoke-SqlCmd or use Get-Content to read the file and run against another server.

PowerShell Script to Generate SQL Agent Job Script and Delete Job

The below function is similar, but drops the job after we script it (useful when backing up jobs before removing) and also includes logic to save jobs by their server and instance name. Instead of saving a server and instance name like OURSERVER\OURINSTANCE, we save it as OURSERVERINSTOURINSTANCE with the name of the job after an underscore.

Function Script-JobsDrop { 
    Param(
    [string]$server
    , [string]$smolibrary
    , [string]$name
    , [string]$path
    )
    Process
    {
        $nl = [Environment]::NewLine
        Add-Type -Path $smolibrary
        $jobserver = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
        foreach ($job in $jobserver.JobServer.Jobs | Where-Object {$_.Name -like "$name*"})
        {
            $fileservername = $server -replace "\\","INST"
            $newfile = $path + $fileservername + "_" + $job.Name + ".sql"
            New-Item $newfile -ItemType file | Out-Null
            "----$server" + $nl + "USE msdb" + $nl + "GO" + $nl + $nl + $job.Script() | Out-file $newfile
            $job.Drop()
        }
    }
}
$smo = "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Script-JobsDrop -server "OURSERVER\OURINSTANCE" -smolibrary $smo -name "" -path "C:\OurFolder\Removed\"

PowerShell Script to Get All SQL Agent Jobs by Category

If we use category for organizing jobs, we can also script jobs by category. If categories do not cover our needs to filtering out unnecessary jobs, we can also filter by description, replacing Where-Object {$_.Category -like “$category”} with Where-Object {$_.Description -like “$category”}. For instance, if we use the job server to run SSIS packages, and add SSIS in the description for all our SSIS packages, we could pass in SSIS and it would filter out jobs that didn’t include SSIS packages.

Function Script-JobsByCategory { 
    Param(
    [string]$server
    , [string]$smolibrary
    , [string]$category
    , [string]$path
    )
    Process
    {
        $nl = [Environment]::NewLine
        Add-Type -Path $smolibrary
        $jobserver = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
        foreach ($job in $jobserver.JobServer.Jobs | Where-Object {$_.Category -like "$category"})
        {
            $newfile = $path + $job.Name + ".sql"
            New-Item $newfile -ItemType file | Out-Null
            "USE msdb" + $nl + "GO" + $nl + $nl + $job.Script() | Out-file $newfile
        }
    }
}
$smo = "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Script-JobsByCategory -server "OURSERVER\OURINSTANCE" -smolibrary $smo -category "Database Maintenance" -path "C:\OurFolder\"

PowerShell Scrip to Get all SQL Server Jobs that are Currently Running

We can also get job information, such as jobs that aren’t idle. If we’re in the process of tracking an issue, it may be helpful to get the active jobs to use our process of elimination to determine what’s causing an issue. The below example also shows how you can use logins and passwords with the SMO library, if you’re in a connection context that does not use a trusted connection. You can make this step even easier by storing these data in a configuration table, then calling the script by only passing in the server name, where the username and password will originate from the configuration table.

Function Get-ActiveJobs { 
    Param(
    [string]$server
    , [string]$smolibrary
    , [string]$username
    , [string]$password
    )
    Process
    {
        Add-Type -Path $smolibrary
        $jobserver = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$scon = "Data Source=$server;Initial Catalog=master;User ID=$username;Password=$password;"
$servercon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$servercon.ConnectionString = $scon
$jobserver = New-Object Microsoft.SqlServer.Management.SMO.Server($servercon)
        foreach ($job in $jobserver.JobServer.Jobs | Where-Object {$_.CurrentRunStatus -ne "Idle"})
        {
            $job.Name
### If we want to stop it:
# $job.Stop()
### If we had searched for jobs that were idle and wanted to run them:
# $job.Start()
        }
    }
}
$smo = "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Get-ActiveJobs -server "OURSERVER\OURINSTANCE" -smolibrary $smo

In addition to returning the name of the job – which is useful when debugging – we can stop the job ($job.Stop()), or if looking for jobs that are idle, but should be running, start the job ($job.Start()).

Next Steps

Evaluate naming conventions, categories or descriptions. These scripts become more useful and faster with excellent organization.
If you’re in a mixed connection environment, using a configuration table can save time when calling the above scripts, in addition to updating the values if usernames or passwords change.
Check out these other PowerShell scripts

Resources

Community

Subscribe