1

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?

  • 1
    What version of SQL Server are you using? – aduguid Aug 14 '18 at 3:51
2

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
|improve this 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"}
|improve this answer|||||
  • 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? – Sri Aug 17 '18 at 1:39
  • You could probably look at the source code that they post in git for that command and copy paste it into a powershell script. – Shaulinator Aug 17 '18 at 1:40
  • 2
    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. – user507 Aug 17 '18 at 13:14
0

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.