Book Image

PowerShell for SQL Server Essentials

By : Donabel Santos
Book Image

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Table of Contents (15 chapters)
PowerShell for SQL Server Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Implementing Reusability with Functions and Modules
Index

PowerShell cmdlets


At the heart of PowerShell is a cmdlet (pronounced as commandlet). A cmdlet is described in MSDN (available at http://msdn.microsoft.com/en-us/library/ms714395(v=vs.85).aspx) as:

"… a lightweight command that is used in the Windows PowerShell environment.

… cmdlets perform an action and typically return a Microsoft .NET Framework object to the next command in the pipeline."

In other words, cmdlets get the job done in PowerShell. You can think of cmdlets as small commands—very specific commands—which you can use to accomplish your task.

To explore the cmdlets available in your PowerShell version, you can use the Get-Command cmdlet. You can filter the results as well. For example, if you want to look for log-related cmdlets, you can use the following command:

Get-Command –Name "*Log*"

Cmdlet naming convention

Cmdlets have a very specific naming convention. They follow the Verb-Noun format and they are typically self-explanatory. More specifically, it is Verb-SingularNoun.

The following are some example cmdlets available in PowerShell:

  • Get-Service

  • Test-Path

  • Set-Content

  • ConvertTo-Csv

Note that cmdlet names are self-documenting. You don't really have to guess what the Get-Service cmdlet does; it gets the corresponding services in your system.

You can get a list of legal, endorsed verbs by Microsoft using the Get-Verb cmdlet. Granted, not all the terms you see are really verbs, but for our purposes, we will treat them as such. For example, Microsoft uses the New verb to create new items:

  • New-Service

  • New-Event

  • New-Object

Another verb that Microsoft considers is Out, mostly used for output. Take a look at the following examples:

  • Out-File

  • Out-GridView

  • Out-Null

Cmdlet parameters

Note that cmdlets can accept parameters or switches. This makes cmdlets quite flexible. You can supply parameters to cmdlets by specifying a dash followed by a parameter name, space, and the parameter value:

Cmdlet -ParameterName ParameterValue -ParameterName ParameterValue

It will be easier to understand how parameters work if we go through an example. Let's take a look at the usage syntax for Get-Service:

Each block in the help section, shown in the preceding screenshot, represents a parameter set. Each parameter set specifies different combinations of parameters and switches that are all valid when you use Get-Service.

Note

Anything in square brackets is optional; anything between angle brackets is mandatory.

Let's consider the following first line of command:

    Get-Service [[-Name] <String[]>] [-ComputerName <String[]>]

The [[-Name] <String[]>]part means that you can specify -Name, which should be your parameter name:

Get-Service –Name *SQL*

Since [[-Name] <String[]>] is surrounded by square brackets, it means it's optional. This parameter name can be left out and you can provide just the value. This makes it positional, meaning the value you provide will map to the parameter defined for that cmdlet at that position. In the following example, the first value will be mapped to the first parameter for Get-Service:

Get-Service *SQL*

The next part [-ComputerName <String[]>] is still overall an optional parameter. However, if you decide to supply the value, you have to specify the parameter name, which is ComputerName. Note that there is no square bracket around ComputerName.

When you specify parameter names, you can also take shortcuts. You can specify just the first few characters of the parameter name, and as long as it's unique, PowerShell will figure out which parameter you are referring to:

Get-Service –Na *SQL*

Note

Although it's quite tempting to use shortcuts, when you are first learning how to use PowerShell, try to always completely spell out the parameter names. This will make your code more readable and easier for the rest of your team to work with your code.

If you have a cmdlet that requires input and you don't provide it, you will be prompted for the values interactively:

Cmdlet aliases

Some of the cmdlets also have aliases by default. This means these cmdlets can be invoked by using a different name than their formal cmdlet name. For example, the following screenshot shows the aliases for Get-ChildItem:

You can also create your own aliases using New-Alias. Aliases can be useful because in some ways, they allow you to use some of the terms you may already be familiar with and leverage them in PowerShell. Aliases also let you personalize PowerShell to your liking. Be careful not to create too many of these though; it may make your PowerShell scripts confusing and even unreadable to others.