New PowerShell Commands from the SQL Server Team!

Have you seen the SQL Server Tools team’s post about the PowerShell module for SQL Server? There are TWENTY FIVE new cmdlets! TWENTY FIVE! That’s a 53% increase over the previous 46 cmdlets. It looks like the SQL Server community is well on its way to becoming proper citizens in the PowerShell world, and we’ll reach Exchange’s 700+ in no time ;)

Included in the 25 new cmdlets is a whole lot more support for SQL Server Agent. This batch was chosen because of community upvoting on the Trello boards. We literally asked for this and got it. If you haven’t participated in the design of the cmdlets yet, head over now and start upvoting.

SQL Server Agent

It’s now so much easier to work with SQL Server Agent jobs. But before we dig in, here are the five new cmdlets dedicated to SQL Server Agent.

Get-SqlAgent Returns a SQL Agent (JobServer) object that is present in the target instance of the SQL Server.
Get-SqlAgentJob Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent.
Get-SqlAgentJobHistory Returns the JobHistory present in the target instance of SQL Agent.
Get-SqlAgentSchedule Returns a JobSchedule object for each schedule that is present in the target instance of SQL Agent Job.
Get-SqlAgentJobStep Returns a SQL JobStep object for each step that is present in the target instance of SQL Agent Job.
Get-SqlAgentSchedule Returns a SQL JobSchedule object for each schedule that is present in the target instance of SQL Agent.

One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server — only your workstation. That’s because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.

As an aside, you may be wondering what a cmdlet is, and what’s the difference between a cmdlet and what you and I write. A cmdlet is a formally written PowerShell command that’s written in C#. I’ve never written one in my life. Instead, I write Functions. They’re both essentially the same, but a cmdlet is fancier on the back end.

Let’s see some code!

Let’s start by specifying our SQL Server name dynamically so that you can easily copy and paste from this blog. In my case, $sqlserver = “sql2012”

Now you can pipe the Agent server to the next command, but you can also explicitly specify the SQL Server in the cmdlet itself. You can also specify the -Since the get events that have occurred since a specific date. Valid values incldue Midnight, Yesterday, LastWeek and LastMonth. But you don’t have to remember this, you can just tab through the values after typing -Since.

Want to know all the jobs associated with a specific schedule? While you can do this to get the schedules.

What if you have duplicate names? Let’s pick with Out-GridView and pass that to Get-SqlAgentJob.

And finally, want to find the failed step within a failed job? Here’s one way you can do it.

In the future, we’ll have corresponding Set cmdlets that can address any issues that are found. Until then, we have these super useful cmdlets that make it way easier to work with jobs in PowerShell.

Great work, team! Want to see more? Aaron Nelson wrote and Rob Sewell wrote a lengthy article about new cmdlets. I wrote up a lil something about them, too.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*