How to find the list of SQL Server jobs using PowerShell?

Question

I need to create Windows PowerShell Scripts that will check and list down all SQL Jobs scheduled on a given database server.

Is it possible to find out the list using PowerShell Scripts? If yes, how to do it?

• aduguid: What version of SQL Server are you using?

Answer 1

I often have to work directly on the Windows host running SQL Server where DBATools, and other handy add-ons aren't available. Here's an easy way to get your information.
The Add-Type below is set for a computer running SSMS 2016. You can either adjust it for your version or skip it altogether and just use the old way of loading the assembly from the Catch block. After that create a server object to the SQL Server you want to check and finally list the jobs and whether they have a schedule or not. The | Where-Object bit from Shaulinator's answer still works to only list scheduled jobs.

Try {
 Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" 
}
Catch {
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
}
$serverObject = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'YourSQLServerNameHere'
$serverObject.JobServer.Jobs |Select Name, HasSchedule

Answer 2

The easiest way is to install the DBATools library and use the command Get-DbaAgentJob.

To return all SQL Agent Job on the local default SQL Server instance.

Get-DbaAgentJob -SqlInstance localhost

There is no explicit command to filter all scheduled jobs on the server, so we can use common features at the end of the script.

Get-DBAAgentJob -SqlInstance localhost -NoDisabledJobs | Where-Object {$_.HasSchedule -Match "True"}

• Sri: Thanks a lot for your kind response. We can't install any third-party tools in our server without client approval. Is there any other way option available?
• Shaulinator: You could probably look at the source code that they post in git for that command and copy paste it into a powershell script.
• user507: Copy/Paste will not work as-is because the module is written with internal/private functions as well. I'm a major contributor to the module and also a consultant with clients that have very high security standards. This is not considered a 3rd party tool, it contains the same library/DLLs that are in SSMS. It is also licensed as MIT so you can label as your own tool if you want.

Answer 3

You can install the SqlServer PowerShell module from Microsoft then use the Get-SqlAgentJob cmdlet to retrieve the information.