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

Scripting basics


Let's get a few syntax basics down. This section is not meant to be an exhaustive tutorial on PowerShell's syntax but should serve as a good, brief introduction.

Let's walk through the following script:

$currdate = (Get-Date -Format "yyyyMMdd hhmmtt")
$servers = @("ROGUE", "CEREBRO")

#save each server's running services into a file
$servers  |
ForEach-Object {

    $computername = $_
    Write-Host "`n`nProcessing $computername"

    $filename = "C:\Temp\$($computername) - $($currdate).csv"

    Get-Service -ComputerName $computername |
    Where-Object -Property Status -EQ "Running" |
    Select-Object Name, DisplayName |
    Export-Csv -Path $filename -NoTypeInformation

}

Even if you are not very familiar with PowerShell yet, you may already be able to tell what the preceding script is trying to accomplish. Simply put, the script iterates over the listed servers and saves the list of running services into a file that acts as a timestamp.

This line creates a variable called $currdate that gets the current system date in the "yyyyMMdd hhmmtt" format:

$currdate = (Get-Date -Format "yyyyMMdd hhmmtt")

The snippet with an at (@) sign, @("ROGUE", "CEREBRO"), creates an array, which is then stored in another variable called $servers:

$servers = @("ROGUE", "CEREBRO")

Since $servers contains multiple values, when you pipe it to the Foreach-Object cmdlet, each value is fed into the script block inside Foreach-Object:

#save each server's running services into a file
$servers  |
ForEach-Object {

}

You are also introduced to a few concepts inside the Foreach-Object block.

To get the current pipeline object, you can use $_. The $_, also referred to as $PSItem, is a special variable. It is part of what PowerShell calls automatic variables. This variable only exists and can only be used in the content of a pipeline. The $_ variable contains the current object in the pipeline, allowing you to perform specific actions on it during the iteration:

    $computername = $_

A backtick is an escape character, for example, to add a newline. It is also a line continuation character:

    Write-Host "`n`nProcessing $computername"

Note that the strings are enclosed in double quotes:

    Write-Host "`n`nProcessing $computername"

Strings in PowerShell can also be enclosed in single quotes. However, if you have variables you want to be evaluated within the string, as in the preceding example, you will have to use double quotes. Single quotes will simply output the variable name verbatim.

PowerShell has a subexpression operator, $(). This allows you to embed another variable or expression inside a string in double quotes, and PowerShell will still extract the variable value or evaluate the expression:

$filename = "C:\Temp\$($computername) - $($currdate).csv"

Here is another example that demonstrates when subexpressions will be useful. The expression to get the date that is 10 days from today is as follows:

(Get-Date).AddDays(10)

If we want to display the value this expression returns, you may be tempted to use:

Write-Host "10 days from now is (Get-Date).AddDays(10)"

However, this simply redisplays the expression; it doesn't evaluate it. One way to get around this without using a subexpression would be to create a new variable and then use it in the double-quoted string:

$currdate = (Get-Date).AddDays(10)
Write-Host "10 days from now is $currdate"

With the subexpression, you don't need to create the new variable:

Write-Host "10 days from now is $((Get-Date).AddDays(10))"

The example we walked through should give you a taste of simple scripting in PowerShell.

The following is a table that outlines some of these common scripting components and operators:

Component

Symbol

Description/examples

Single line comment

#

This component allows you to include any comments or documentation about your code; text after # in a line is not executed, for example, #get the current date.

Multiline comment

<#

#>

This allows you to create comments that span multiple lines, as shown in the following example:

<#
  get the current
  date
#>

Backtick

`

Backtick can be used as an escape character:

$name = "Hello `n world!"

This is also a line continuation character; it allows you to break a command into multiple lines—some find it more readable, but beware that some will find it less readable because the backtick character can be conspicuous:

Get-Service `
    -Name *SQL* `
    -ComputerName ROGUE

Dollar sign

$

By default, variables in PowerShell are loosely typed (that is, the data type changes based on the value stored by the variable):

$dt = Get-Date

Single quotes

'

This component allows you to enclose string literals:

$name = 'sqlbelle'

Double quotes

"

This component allows you to enclose string literals:

$name = "sqlbelle"

This component also allows you to expand variables (that is, replace variable names within the string to their values) or interpret escape characters:

$name = "sqlbelle"
$message = "Hello `n $name"

Plus

+

This component is a string concatenation operator:

$name = "sqlbelle"
$message = "Hello " + $name

Dot

.

This component allows you to access properties or methods with the corresponding object:

$dt.AddDays(10)

Subexpression

$()

This component allows you to embed a variable or expression in a double-quoted string; PowerShell evaluates the expression inside this operator:

Write-Host "Date: $($dt.AddDays(10))"

At sign

@()

This component is an array subexpression operator:

@("ROGUE", "CEREBRO")

Square brackets

[]

This component is an index operator. It allows you to access indexed collections (arrays and hash tables):

$servers = @("ROGUE", "CEREBRO")
$servers[0]

It also acts as a casting operator:

[datetime]$dt

Here-String

@"

"@

This component allows you to create a multiline string to assign to a variable without having to break the string into multiple string expressions concatenated by a plus (+) sign. It starts with @" and must end with "@ in a line by itself (no characters or spaces before ending "@):

$x = "@
Hello $name.
This is a multiline
string
"@

The table is not a comprehensive list of operators or syntax about PowerShell. As you learn more about PowerShell, you will find a lot of additional components and different variations from what has been presented here.

Note

To learn more about operators, use Get-Help *Operator* and go through all the available topics. You can also go to the TechNet page specifically for operators, which is available at http://technet.microsoft.com/en-us/library/hh847732.aspx.