Sql-server – How to find the list of SQL Server jobs using PowerShell

jobspowershellsql server

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?

Best Answer

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
Related Question