Most of our recipes will perform possible queries and changes in your SQL Server instance or Windows Server. This will require elevated privileges both on your database side and in the PowerShell side. To ensure you can run the recipes in this book without getting access errors, you will need to execute the console or the ISE as administrator. One way to do this is by right-clicking on the PowerShell icon in your task bar and selecting to run either program as administrator.

You can confirm that you've launched either program as administrator by checking the title bar. You should see Administrator added to your title bar.

The Execution Policy settings in PowerShell determine what is allowed or not allowed to be run in PowerShell.

For security reasons, PowerShell will not run automatically unless it is authorized in the settings. This is to prevent scripts from different sources, for example, the ones downloaded from the Internet, from potentially running malicious or destructive code.

To run the recipes in this book, you will need at least a RemoteSigned setting. To get this, run the following code:

Set-ExecutionPolicy RemoteSigned

If you save your PowerShell code in a file, you need to ensure it has a .ps1 extension. Otherwise, PowerShell will not run it. Unlike traditional scripts, you cannot run a PowerShell script by double clicking the file. Instead, you can run this script from the PowerShell console simply by calling the name. For example, if you have a script called myscript.ps1 located in the C:\Scripts directory, you can provide the full path to the file to invoke it:

PS C:\> C:\Scripts\myscript.ps1

You can also change your directory to where the script is saved, and invoke it like this (notice there is a dot and backslash in front of the file):

PS C:\Scripts> .\myscript.ps1

If the file or path to the file has spaces, then you will need to enclose the full path and file name in single or double quotes. Before PowerShell v3, you would need to use the call (&) operator prior to the script name. From PowerShell v3 onwards, you do not need to specify the call operator anymore, but it will still work if you do:

PS C:\Scripts> & '.\my script.ps1'

If you want to retain the variables and functions included in the script in memory, so that it's available in your session globally, then you will need to dot source the script. Dot source means prepending the filename, or path to the file, with a dot and a space:

PS C:\Scripts> . .\myscript.ps1
PS C:\Scripts> . '.\my script.ps1'

If you are running PowerShell v3, v4, or v5, you can choose to run an older version of PowerShell. To do this, simply invoke the shell or start your session with a -Version parameter and provide the version you want to use:

Powershell.exe -Version 2

You can check that the change was made by using the $PSVersionTable variable. You should now see the PSVersion value reverted to the value you provided to the –Version parameter:

Understanding how line continuation works in PowerShell will be crucial when working with the recipes in this book.

You will encounter a line of PowerShell code that may be wider than the width of the page you are reading. For example, consider the following code:

#create your SQL Server SMO instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

The preceding code, which creates a SQL Server instance, is meant to be written in a single line (no line breaks), otherwise it will not execute. However, to make the line more readable, some snippets may be broken into multiple lines as long as the line continuation character, a backtick (`), is provided at the end of the line just before the carriage return. Check out the following code:

$server = New-Object `
         -TypeName `
          Microsoft.SqlServer.Management.Smo.Server `
         -ArgumentList $instanceName

Adding the line breaks cleans up the code a little bit and makes it more readable. But do you see the backtick at the end of each line? You probably have to squint to see it. It's probably not obvious that the backtick is the last character before the carriage return.

For this book, I will try to avoid backticks for line continuation. Please assume that a long line of code in the recipes, although wrapping in your page, should be written as just a single line. You can also confirm the syntax by downloading the code from the Packt Publishing website.

Where possible, I will break down the code into more readable chunks without using the backtick. For example, consider a long line of code like this:

$server.Databases | Get-Member -MemberType "Property" | Where-Object Definition -Like "*Smo*"

The preceding line can be rewritten into multiple lines using the pipe (|) operator at the end of each line:

$server.Databases |
Get-Member -MemberType "Property" |
Where-Object Definition -Like "*Smo*"

If I have to use the backtick, I will call your attention to it in the code comments.

Modules are a way to extend PowerShell. Modules can add cmdlets and providers, and load functions, variables, aliases and other tools to your session.

For our recipes, we will use the SQLPS module a lot. To load this module, you can use the Import-Module cmdlet:

Import-Module SQLPS

Note that running this command will change your current working directory to:

PS SQLSERVER:>