Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using SMO With PowerShell To Obtain SQL Server Job Information


By:   |   Last Updated: 2015-07-21   |   Comments (1)   |   Related Tips: More > SQL Server Agent

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


Last Updated: 2015-07-21


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 21, 2015 - 11:55:04 AM - Steve Wang Back To Top

When using acronyms in your writings, provide their definitionI.E. "we will look at the SMO (SQL Server Manangement Objects) library with PowerShell."


As a developer use these tips to quickly educate myself. 


Thank you


Learn more about SQL Server tools