Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
PowerShell for SQL Server Essentials

You're reading from   PowerShell for SQL Server Essentials Manage and monitor SQL Server administration and application deployment with PowerShell

Arrow left icon
Product type Paperback
Published in Feb 2015
Publisher Packt
ISBN-13 9781784391492
Length 186 pages
Edition 1st Edition
Arrow right icon
Author (1):
Arrow left icon
Donabel Santos Donabel Santos
Author Profile Icon Donabel Santos
Donabel Santos
Arrow right icon
View More author details
Toc

Table of Contents (9) Chapters Close

Preface 1. Getting Started with PowerShell FREE CHAPTER 2. Using PowerShell with SQL Server 3. Profiling and Configuring SQL Server 4. Basic SQL Server Administration 5. Querying SQL Server with PowerShell 6. Monitoring and Automating SQL Server A. 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:

Cmdlet parameters

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 parameters

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:

Cmdlet aliases

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.

You have been reading a chapter from
PowerShell for SQL Server Essentials
Published in: Feb 2015
Publisher: Packt
ISBN-13: 9781784391492
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image