Art of the DBA Rotating Header Image

A Month of SQLPS: The SQL cmdlets

There’s more to the SQLPS module than just a fancy way to browse SQL Server components. While the provider gives us an effective way to interact with different sections of our database environment, we need more. That’s why there is a series of cmdlets included in the module to manage things like backups and availability groups, as well as retrieve SMO objects without having to always rely on provider paths or complex SMO objects.

To get a listing of all cmdlets is the same as with any Powershell module: Get-Command -Module SQLPS. This will list out the 45 cmdlets in the module along with the provider:

Get-Command -Module SQLPS

SQLPS-9-1

While I want to spend the next week or so covering these cmdlets in detail, I want to begin by re-emphasizing the foundational elements of the language will help us figure out these cmdlets on our own. We’ve already seen how we can see what cmdlets are available to us by using Get-Command, allowing to narrow down our focus for what we might be searching for.

Next, if we examine the names of the cmdlets, we will have a general idea of what they do based on the verb plus noun naming convention. By using a little pipeline magic, we can see what the different verb options are:

get-command -Module SQLPS | Group-Object verb

SQLPS-9-2

We can use the verb list to further narrow down the cmdlets. If we’re looking for a cmdlet to create something, we’d look at cmdlets starting with ‘New-’. If we wanted to retrieve an object, we’d look for a cmdlet named ‘Get-’. This continues on, but helps us intuitively narrow down the cmdlets by what they do.

Finally, once we have a cmdlet in mind, we can look at the specific help file to learn how that cmdlet is used with the Get-Help cmdlet. I usually recommend calling Get-Help with the -ShowWindow switch because it gives us a separate window where the help information is displayed:

Get-Help Get-SqlInstance -ShowWindow

Unfortunately, this is where we run into some additional problems with the module. While the cmdlets available with the core installation of Powershell have some pretty detailed help, the help files for the module are thin and incomplete. Many cmdlets have only basic descriptions and no information on outputs . This means we’re going to have to use a little detective work to evaluate these cmdlets and figure out what they do.

This is the main reason why I wanted to write this series. There’s actually some useful functionality built here, but the lack of documentation is a real struggle and leads to many database folks not using these cmdlets because of the lack of explanation around them. Some will turn to the SMO and write a lot of code to accomplish the same tasks. Others will simply give up on Powershell completely and turn to other tools. Stick around over the next few weeks and we’ll try and fill this gap, showing you what’s availble, how they work, and help build some patterns for their use.

2 Comments

  1. Tom says:

    Loving these posts! Keep up the great work.

  2. […] A Month of SQLPS: The SQL cmdlets | Art of the DBA […]

Leave a Reply to Tom Cancel reply

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