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
SQL Server 2012 with PowerShell V3 Cookbook

You're reading from   SQL Server 2012 with PowerShell V3 Cookbook

Arrow left icon
Product type Paperback
Published in Oct 2012
Publisher Packt
ISBN-13 9781849686464
Length 634 pages
Edition 1st Edition
Concepts
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 (21) Chapters Close

SQL Server 2012 with PowerShell V3 Cookbook
Credits
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
1. Getting Started with SQL Server and PowerShell FREE CHAPTER 2. SQL Server and PowerShell Basic Tasks 3. Basic Administration 4. Security 5. Advanced Administration 6. Backup and Restore 7. SQL Server Development 8. Business Intelligence 9. Helpful PowerShell Snippets SQL Server and PowerShell CheatSheet PowerShell Primer Resources Creating a SQL Server VM Index

Scripting syntax


We will now dive into the specifics of PowerShell syntax.

Statement terminators

A semicolon is typically a mandatory statement terminator in many programming and scripting languages. PowerShell considers both a newline and a semicolon as statement terminators, although using the newline is more common, that's why you won't see a lot of semicolons in most PowerShell scripts. There is a caveat for using the newline; that is, the previous line must be a complete statement before it gets executed.

Escape and line continuation

The backtick (`) is a peculiar character in PowerShell, and it has double meaning. You can typically find this character in your keyboard above the left Tab key, and is in the same key as the tilde (~) symbol.

The backtick is the escape character in PowerShell. Some of the common characters that need to be escaped are:

Escaped Character

Description

`n

Newline

`r

Carriage return

`'

Single quote

`"

Double quote

`0

Null

PowerShell also uses the backtick as a line continuation character. You may find yourself writing a long chain of commands and may want to put different parts of the command onto different lines to make the code more readable. If you do, you need to make sure to put a backtick at the end of each line you are continuing, otherwise PowerShell treats the newline as a statement terminator. You also need to make sure there are not any extra spaces after the backtick:

Invoke-Sqlcmd `
-Query $query `
-ServerInstance $instanceName `
-Database $dbName

Variables

Variables are placeholders for values. Variables in PowerShell start with a dollar ($) sign.

$a = 10

By default, variables are loosely and dynamically typed—meaning the variable assumes the data type based on the value of the content:

$a = 10
$a.GetType()  #Int32

$a = "Hello"
$a.GetType()  #String

$a = Get-Date
$a.GetType()  #DateTime

Note how the data type changes based on the value we assign to the variable. You can however create strongly typed variables.

[int]$a = 10
$a.GetType()  #Int32

When we have strongly typed variables, we can no longer just haphazardly assign it any value. If we do, we will get an error:

$a = "Hello"

<# Error
Cannot convert value "Hello" to type "System.Int32". Error: "Input string was not in a correct format."
At line:3 char:1
+ $a = "Hello"
+ ~~~~~~~~~~~~
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException
#>

We have also mentioned in the previous section that PowerShell is object oriented. Variables in PowerShell are automatically created as objects. Depending on the data type, variables are packaged with their own attributes and methods. To explore what properties and methods are available with a data type, use the Get-Member cmdlet:

Here-string

There may be times when you need to create a string variable that will contain multiple lines of code. You should create these as here-string.

A here-string is a string that often contains large blocks of text. It starts with @" and must end with a line that contains only "@. For the here-string terminating character pair, make sure this is placed in its own line, and there are no other characters and no spaces before or after it.

$query = @"
INSERT INTO SampleXML
(FileName,XMLStuff,FileExtension) 
VALUES('$xmlfile','$xml','$fileextension')
"@ 

String interpolation

When working with strings, you need to remember that using a double quote evaluates enclosed variables, that is variables are replaced with their values. For example:

$today = Get-Date
Write-Host "Today is $today"

#result 
#Today is 06/12/2012 19:48:24

This behavior may sometimes cause issues especially if you need to use multiple variables in continuation, as in the following case where we want to combine $name, and underscore (_), $ts and .txt to create a timestamped filename.

$name = "belle"
$ts = Get-Date -Format yyyy-MMM-dd
$filename = "$name_$ts.txt"

This will give an incorrect result, because it will look for $name_ and $ts, but since it cannot find $name_, the final filename we get is 2012-Jun-06.txt and not belle_2012-Jun-06.txt.

To resolve this issue, we can use any of the following to ensure proper interpolation:

$filename = "$($name)_$($ts).txt"
Write-Host $filename

$filename = "${name}_${ts}.txt"
Write-Host $filename

$filename = "{0}_{1}.txt" -f $name, $ts
Write-Host $filename 

A single quote, on the other hand, preserves the actual variable name and does not interpolate the value:

$today = Get-Date
Write-Host 'Today is $today'

#result 
#Today is $today

You can also store actual commands in a string. However, this is treated as a string unless you prepend it with an ampersand (&)‑which is PowerShell's invoke or call operator.

$cmd = "Get-Process"

$cmd    #just displays Get-Process, treated as string
&$cmd   #actually executes Get-Process

Operators

The operators used in PowerShell may not be readily familiar to you even if you have already done some programming before. This is because the operators in PowerShell do not use the common operator symbols.

PowerShell

Traditional Operator

Description

-eq

==

Equal to

-ne

<> or !=

Not equal to

-match

-notmatch

 

Match using regex; searches anywhere in string

-contains

-notcontains

 

Collection match. Does item exist in array or collection?

-like

-notlike

 

Wildcard match

* (asterisk) for zero or more characters

? (question mark) for any single character

-clike

-cnotlike

 

Case—sensitive wildcard match

-not

!

Negation

-lt

<

Less than

-le

<=

Less than or equal to

-gt

>

Greater than

-ge

>=

Greater than or equal to

-and

&&

Logical and

-or

||

Logical or

-bor

|

Bitwise or

-band

&

Bitwise and

-xor

^

Exclusive or

Note that many operators perform case-insensitive string comparisons by default. If you want to do case-sensitive matching, prepend with c. For example, –ceq, -clike, -cnotlike.

Displaying messages

Often we will need to display or log messages as our scripts execute. PowerShell provides a few cmdlets to help us accomplish this.

Get-Command -Name "*Write*" -CommandType Cmdlet 

This should give a list of our Write- related cmdlets:

Cmdlet

Description

Write-Debug

Display debug message to console

Typically used with

$DebugPreference = "Continue"

Write-Error

Display non-terminating error message to console

Write-EventLog

Write message to Windows Event Log

Write-Host

Display string message to host

Write-Output

Write an object to pipeline

Write-Progress

Display a progress bar

Write-Verbose

Display verbose message to console

Typically used with

$VerbosePreference = "Continue"

Write-Warning

Display warning message to console

Although some of these cmdlets seem similar, there are some fundamental differences. For example, Write-Host and Write-Output seem to display the same messages on screen. Write-Host however simply displays a string, but Write-Ouput writes objects that have properties that can be queried, and can eventually be used in the pipeline.

We use Write-Verbose a fair bit in the recipes in this book. Write-Verbose does not automatically display messages on the host. It relies on the $VerbosePreference setting. By default, $VerbosePreference is set to SilentlyContinue, but it can also be set to Continue, which allows us to display messages used with Write-Verbose to screen.

$VerbosePreference = "Continue"
$folderName = "C:\BLOB Files\"

#using PowerShell V2 style Where-Object syntax
Get-ChildItem $folderName | 
Where-Object {$_.PSIsContainer -eq $false}  |
ForEach-Object {
   $blobFile = $_
   Write-Verbose "Importing file $($blobFile.FullName)..."
}
$VerbosePreference = "SilentlyContinue"

This is an elegant way of turning all messages on or off, without needing to change the script. This can also be used as a switch and can be passed to the script or a function.

Comments

Comments are important in any programming or scripting language. Comments are often used to document logic, and sometimes a chain of changes to the script.

Single line comments start with a hash sign (#):

#this is a single line comment

Block comments start with <# and end with #>:

<#
this is a block comment
#>

PowerShell also supports what's called Comment Based Help. This feature allows you to put a special comment block at the start of your script, or in the beginning of your function, that allows the script or function to be looked up using Get-Help. A sample of this type of comment block follows:

<#
.SYNOPSIS
   Creates a full database backup
.DESCRIPTION
   Creates a full database backup using specified instance name and database name
   This will place the backup file to the default backup directory of the instance
.PARAMETER instanceName
   instance where database to be backed up resides
.PARAMETER databaseName
   database to be backed up
.EXAMPLE
   PS C:\PowerShell> .\Backup-Database.ps1 -instanceName "QUERYWORKS\SQL01" -databaseName "pubs"
.EXAMPLE
   PS C:\PowerShell> .\Backup-Database.ps1 -instance "QUERYWORKS\SQL01" -database "pubs"
.NOTES
   To get help:
   Get-Help .\Backup-Database.ps1
.LINK
   http://msdn.microsoft.com/en-us/library/hh245198.aspx
#>

To look up the help, you can simply type a Get-Help followed by the script filename, or the function name:

PS>Get-Help .\Backup-Database.ps1

Special variables

PowerShell also has some special variables. These special variables do not need to be created ahead of time, they are already available. Some of the special variables are:

Special Variable

Description

$_

Current pipeline object

$args

Arguments passed to a function

$error

Array that stores all errors

$home

User's home directory

$host

Host information

$match

Regex matches

$profile

Path to profile, if available

$PSHome

Install directory of PowerShell

$PSISE

PowerShell Scripting Environment object

$pid

Process ID (PID) of PowerShell process

$pwd

Present working directory

$true

Boolean true

$false

Boolean false

$null

Null value

Conditions

PowerShell supports conditional logic using if/else statements or switch statements. These two constructs allow you to check for a condition, and consequently execute different blocks of code if the condition is met or not.

Let's look at an example of an if/else block:

$answer = Read-Host "Which course are you taking?"
if ($answer -eq "COMP 4677")
{
   Write-Host "That's SQL Server Administration"
}
elseif ($answer -eq "COMP 4678")
{
   Write-Host "That's SQL Server Development"
}
else
{
   Write-Host "That's another course"
}

Note that the elseif and else blocks are optional. They don't need to be defined if you do not have a separate code to execute if the condition is not met.

An equivalent switch block can be written for the above code:

$answer = Read-Host "Which course are you taking?"
switch ($answer)
{
    "COMP 4677"
    {
       Write-Host "That's SQL Server Administration"
    }
    "COMP 4678"
    {
       Write-Host "That's SQL Server Development"
    }
    default
    {
        Write-Host "That's another course"
    }
}

Note that these two constructs can be functionally equivalent for simple comparisons. The choice to use one over the other hinges on preference and readability. If there are many choices, the switch can definitely make the code more readable.

Regular Expressions

Regular expressions, more commonly referred to as regex, specify a string pattern to match. Regex can be extremely powerful, and is often used when dealing with massive amounts of text. The area of bioinformatics, for example, tends to rely heavily on regular expressions for gene pattern matching.

Regex can also be quite confusing especially for beginners. It has its own set of patterns and wildcards, and it is up to you to put these together to ensure you are matching what you need to be matched.

Note

See the recipe Testing Regular Expressions in Chapter 9, Helpful PowerShell Snippets.

Arrays

Arrays are collections of items. Often we find ourselves needing to store a group of items, either for further processing, or for exporting.

#ways to create an array
$myArray = @() #empty
$myArray = 1,2,3,4,5
$myArray = @(1,2,3,4,5)

#array of processes consuming >30% CPU
$myArray = (Get-Process | Where  CPU -gt 30 )

Arrays can either be of a fixed size or not. Fixed-size arrays are instantiated with a fixed number of items. Some of the typical methods such as Add or Remove cannot be used with fixed-size arrays:

$myArray = @()
$myArray += 1,2,3,4,5
$myArray += 6,7,8
$myArray.Add(9) #error because array is fixed size

Removing an item from a fixed array is a little bit tricky. Although arrays have Remove and RemoveAt methods—to remove based on value and index respectively—we cannot use these with fixed-size arrays. To remove an item from a fixed-size array, we will need to reassign the new set of values to the array variable.

#remove 6
$myArray = $myArray -ne 6

#remove 7
$myArray = $myArray -ne 7

To create a dynamic-sized array, you will need to declare the array as an array list, and add items using the Add method. This also supports removing items from the list using the Remove method.

$myArray = New-Object System.Collections.ArrayList
$myArray.Add(1)
$myArray.Add(2)
$myArray.Add(3)
$myArray.Remove(2)

We can use indices to retrieve information from the array:

#retrieve first item
$myArray[0]

#retrieve first 3 items
$myArray[0..2]

We can also retrieve based on some comparison or condition:

#retrieving anything > 3
$myArray -gt 3

Hashes

A hash is also a collection. This is different from an array, however, because hashes are collections of key-value pairs. Hashes are also called associative arrays, or hash tables.

#simple hash
$simplehash = @{
  "BCIT" = "BC Institute of Technology"
  "CST" = "Computer Systems Technology"
  "CIT" = "Computer Information Technology"
}
$simplehash.Count

#hash containing process IDs and names
$hash = @{}
Get-Process | Foreach {$hash.Add($_.Id, $_.Name)}
$hash.GetType()

To access items in a hash, we can refer to the hash table variable, and retrieve based on the stored key:

$simplehash["BCIT"]
$simplehash.BCIT

Loop

A loop allows you to repeatedly execute block(s) of code based on some condition. There are different types of loop support in PowerShell. For all intents and purposes, you may not need to use all of these types, but it's always useful to be aware of what's available and doable.

There is a while loop, where the condition is tested at the beginning of the block:

$i = 1;
while($i -le 5)
{
   #code block
   $i
   $i++
}

There is also support for the do while loop, where the condition is tested at the bottom of the block:

$i = 1
do
{
   #code block
   $i
   $i++
}while($i -le 5)

The for loop allows you to loop a specified number of times, based on a counter you create at the for header.

for($i = 1; $i -le 5; $i++)
{
   $i
}

There is yet another type of loop, a foreach loop. This loop is a little bit different because it works with arrays or collections. It allows a block of code to be executed for each item in a collection.

$backupcmds = Get-Command -Name "*Backup*" -CommandType Cmdlet
foreach($backupcmd in $backupcmds)
{
    $backupcmd | Get-Member
}

If you're a developer, this code looks very familiar to you. In PowerShell, however, you can use pipelining to make your code more concise.

Get-Command -Name "*Backup*" -CommandType Cmdlet |
Foreach { $_ | Get-Member}

Error Handling

When developing functions or scripts, it is important to think beyond just the functionality you are trying to achieve. You also want to handle exceptions, or errors, when they happen. We all want our scripts to gracefully exit if something goes wrong, rather than display some rather intimidating or cryptic error messages.

Developers in the house will be familiar with the concept of try/catch/finally. This is a construct that allows us to put the code we want to run in one block (try), exception handling code in another (catch), and any must-execute housekeeping blocks in a final block (finally).

$dividend = 20
$divisor = 0

try
{
    $result = $dividend/$divisor
}
catch 
{
    Write-Host ("======" * 20)
    Write-Host "Exception $error[0]"
    Write-Host ("======" * 20)
    
}
finally
{
    Write-Host "Housekeeping block"
    Write-Host "Must execute by hook or by crook"
}
lock icon The rest of the chapter is locked
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