SQLServerCentral Article

The Basics of PowerShell Day By Day

,

I wrote an article on using PowerShell to call stored procedures, but a friend sent me a few questions in email about how PowerShell works. I have assumed most people have tried it by now, but perhaps not, so I decided to write a few basic items on PowerShell for those getting started.

There is a great Stairway series on PowerShell from Ben Miller, which I enjoyed, but it goes a little quickly. I would defer to the Level 1 article for installing PowerShell (PoSh), and then the other ones for working with SQL Server. Some of the information is older, as the SQLPS module has been deprecated and dbatools is the recommended way to use PoSh and SQL Server, but it's a good series that will teach you something about programming against SQL Server. Highly recommended.

This article will cover some of the basics on how you write programs in PoSh, from the perspective of a DBA that is used to using T-SQL. We'll cover the basics of variables, objects, and pipelines.

Getting Started

Read Ben's Level 1 to install PoSh and set the execution policy. It also covers some basics of running cmdlets. I'm going to look at how I learned some things about PowerShell as a DBA.

First, working in PoSh is similar to how I work in T-SQL. If I write a few commands in SSMS, they get executed in sequence. Same with PoSh. However, most of the time I find myself at a command line trying things out before I assemble things into a program, like a stored procedure.

Variables

In T-SQL, we have to use DECLARE to specify a variable name and type. In the code below, we declare a variable, called @fullname, give it a type and assign it the value of "Steve".

DECLARE @fullname varchar(200)= 'Frank';

This is a strongly typed way of programming. In PoSh, we can create a variable anytime, just by using the $ prefix and giving it a name. We don't specify a type, and in general, all variables, are objects. That means they have properties and methods  we can use.

If I repeat the same type of variable assignment above in PoSh, I'd do this:

$Fullname = "Frank"

I can tell this is an object, if I run this through Get-Member, which will let me know the properties and methods. Here is the call and the result.

$Fullname | Get-Member
   TypeName: System.String                                                                                                     
                                                                                                                               
Name             MemberType            Definition                                                                              
----             ----------            ----------                                                                              
Clone            Method                System.Object Clone(), System.Object ICloneable.Clone()                                 
CompareTo        Method                int CompareTo(System.Object value), int CompareTo(string strB), int IComparable.CompareT
Contains         Method                bool Contains(string value)                                                             
CopyTo           Method                void CopyTo(int sourceIndex, char[] destination, int destinationIndex, int count)       
EndsWith         Method                bool EndsWith(string value), bool EndsWith(string value, System.StringComparison compari
Equals           Method                bool Equals(System.Object obj), bool Equals(string value), bool Equals(string value, Sys
GetEnumerator    Method                System.CharEnumerator GetEnumerator(), System.Collections.IEnumerator IEnumerable.GetEnu
GetHashCode      Method                int GetHashCode()                                                                       
GetType          Method                type GetType()                                                                          
GetTypeCode      Method                System.TypeCode GetTypeCode(), System.TypeCode IConvertible.GetTypeCode()               
IndexOf          Method                int IndexOf(char value), int IndexOf(char value, int startIndex), int IndexOf(string val
IndexOfAny       Method                int IndexOfAny(char[] anyOf), int IndexOfAny(char[] anyOf, int startIndex), int IndexOfA
Insert           Method                string Insert(int startIndex, string value)                                             
IsNormalized     Method                bool IsNormalized(), bool IsNormalized(System.Text.NormalizationForm normalizationForm) 
LastIndexOf      Method                int LastIndexOf(char value), int LastIndexOf(char value, int startIndex), int LastIndexO
LastIndexOfAny   Method                int LastIndexOfAny(char[] anyOf), int LastIndexOfAny(char[] anyOf, int startIndex), int 
Normalize        Method                string Normalize(), string Normalize(System.Text.NormalizationForm normalizationForm)   
PadLeft          Method                string PadLeft(int totalWidth), string PadLeft(int totalWidth, char paddingChar)        
PadRight         Method                string PadRight(int totalWidth), string PadRight(int totalWidth, char paddingChar)      
Remove           Method                string Remove(int startIndex, int count), string Remove(int startIndex)                 
Replace          Method                string Replace(char oldChar, char newChar), string Replace(string oldValue, string newVa
Split            Method                string[] Split(Params char[] separator), string[] Split(char[] separator, int count), st
StartsWith       Method                bool StartsWith(string value), bool StartsWith(string value, System.StringComparison com
Substring        Method                string Substring(int startIndex), string Substring(int startIndex, int length)          
ToBoolean        Method                bool IConvertible.ToBoolean(System.IFormatProvider provider)                            
ToByte           Method                byte IConvertible.ToByte(System.IFormatProvider provider)                               
ToChar           Method                char IConvertible.ToChar(System.IFormatProvider provider)                               
ToCharArray      Method                char[] ToCharArray(), char[] ToCharArray(int startIndex, int length)                    
ToDateTime       Method                datetime IConvertible.ToDateTime(System.IFormatProvider provider)                       
ToDecimal        Method                decimal IConvertible.ToDecimal(System.IFormatProvider provider)                         
ToDouble         Method                double IConvertible.ToDouble(System.IFormatProvider provider)                           
ToInt16          Method                int16 IConvertible.ToInt16(System.IFormatProvider provider)                             
ToInt32          Method                int IConvertible.ToInt32(System.IFormatProvider provider)                               
ToInt64          Method                long IConvertible.ToInt64(System.IFormatProvider provider)                              
ToLower          Method                string ToLower(), string ToLower(cultureinfo culture)                                   
ToLowerInvariant Method                string ToLowerInvariant()                                                               
ToSByte          Method                sbyte IConvertible.ToSByte(System.IFormatProvider provider)                             
ToSingle         Method                float IConvertible.ToSingle(System.IFormatProvider provider)                            
ToString         Method                string ToString(), string ToString(System.IFormatProvider provider), string IConvertible
ToType           Method                System.Object IConvertible.ToType(type conversionType, System.IFormatProvider provider) 
ToUInt16         Method                uint16 IConvertible.ToUInt16(System.IFormatProvider provider)                           
ToUInt32         Method                uint32 IConvertible.ToUInt32(System.IFormatProvider provider)                           
ToUInt64         Method                uint64 IConvertible.ToUInt64(System.IFormatProvider provider)                           
ToUpper          Method                string ToUpper(), string ToUpper(cultureinfo culture)                                   
ToUpperInvariant Method                string ToUpperInvariant()                                                               
Trim             Method                string Trim(Params char[] trimChars), string Trim()                                     
TrimEnd          Method                string TrimEnd(Params char[] trimChars)                                                 
TrimStart        Method                string TrimStart(Params char[] trimChars)                                               
Chars            ParameterizedProperty char Chars(int index) {get;}                                                            
Length           Property              int Length {get;}

As you can see at the top, this is a string type. That means all the methods I can use for strings, like ToLower, ToUpper, etc. are available.

Likewise, I can all a common command, like Get-Module. This will return me the items installed on my system.

PS> Get-Module
ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Script     0.0        chocolateyProfile                   {TabExpansion, Update-SessionEnvironment, refreshenv}
Manifest   3.1.0.0    Microsoft.PowerShell.Management     {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}
Manifest   3.1.0.0    Microsoft.PowerShell.Utility        {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Script     2.0.0      PSReadline                          {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Remove-PSReadLineKeyHan... Manifest   15.0       SQLPS                               {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp

I can assign the results of this to a variable like this:

$Modules = Get-Module

If I run that through Get-Member, I find something different for the type:

PS>$Modules | Get-Member
   TypeName: System.Management.Automation.PSModuleInfo

No change in how I declared the variable, which is much different than T-SQL, but also both fantastic and dangerous. I find object mismatches all the time as I write code, because I'm thinking of these are variables, not objects.

Why Do You Care?

I don't want to delve too deep into the whole why objects matter. Suffice it to say that you should understand that using parameters to store values and to pass into other cmdlets or functions, means that you need to get objects that are compatible.

You mainly care because some of the functions you'll call require the object type to match, as there isn't always a way to convert one object to another.

The good thing is that PoSh will often try to use a property, like Name, in the object if it can as the input for another function.

Reassigning Variables

I can also reassign a variable to a new type, and PoSh handles this. Look at the code below, where we have a string that we then assign an integer as its value.

PS>$Fullname                     
Frank                            
PS>$Fullname = 45                
PS>$fullname |Get-Member         
                                 
                                 
   TypeName: System.Int32

This is good, in that we can easily reuse variables if we want, but it's bad in that we can cause ourselves issues.

The bottom line is be careful of how you use variables in scripts and try to use new ones for each task, and use descriptive names.

Running Code

We don't have an IDE like SSMS for PowerShell, Instead, we have a few options for how we write and run code. Interactively, we can run code from a command line or an IDE like VS Code. From my start menu, I can easily run a PowerShell command line by picking that item.

PoSh cli

This opens a command like window, though the default is blue so that you know this is PoSh, not a normal DOS-style window. The prompt also starts with PS.

powershell window

I

can also open a normal "command prompt" that runs cmd.exe. From there, I can run "powershell" to get into the Powershell prompt.

Powershell from cmd.exe

If I open VS Code, I also have a terminal at the bottom, which defaults to PoSh.

posh terminal in VS Code

From any of these places, I can run some interactive code in PoSh. For example, I can assign a series of variables:

PS C:\Users\frank> $name = "Frank"
PS C:\Users\frank> $age = 40  
PS C:\Users\frank> $title = "DBA"
PS C:\Users\frank> $name + " is " + $age + " and works as a " + $title
Frank is 40 and works as a DBA
PS C:\Users\frank>

PoSh takes care of converting the $age to a string in this case, something that T-SQL doesn't do. This is because T-SQL would try to convert the strings to ints.

We can also put code in a file. If I take those lines and put them in a file in VS Code, I can save this to my file system. The default extension is .ps1, so I'll use that to save this code as frank.ps1. I then run this by calling the filename in PoSh. Note that I need to specify this is in the local folder with a ".\".

running a PoSh file

Note, if you get an error here, it might be because you haven't set the execution policy. You can see how to do that in Ben's first Stairway article.

From a cmd.exe CLI, we can also run code by running powershell.exe and passing in the name of our script as a parameter.

Getting Oriented in PoSh

There are a couple things to think about in PowerShell that I find handy. First, this is a batch language, much like you might write a .cmd or .bat file in a command prompt. Your code in a file executes line by line. Keep that in mind.

Second, much of PoSh is built around functions, called cmdlets. These are of the Verb-Noun category. So there are the verbs, some of which are:

  • Add
  • Copy
  • Find
  • Get
  • Move
  • Set
  • Show

There are others listed on the Microsoft Docs site. This is what software companies, and you, should follow for your own cmdlets.

The Noun part of the cmdlet is descriptive, like Get-Member, or Get-Service. Notice these are singular, unlike the way most of us name our tables. We can guess names also, as the command line interface (CLI) has tab completion. If I type "Get-S" in my generic lab vm, I can hit tab and I'll see:

First tab completion

Then I can hit Tab and I'll see:

second tab completion

I can keep doing and I'll go through Get-SecureBootPolicy, Get-Service, and more. If I keep going, I'll get back to Get-ScheduledJob.

This is great, because I often can't quite remember a specific name, so I can tab through what I know, though to be fair, as I've added the Azure modules to my system, Get-A is fairly useless.

The other thing to be aware of us that PoSh uses the PascalCase for the words. Notice how the first letter of each word in the cmdlet is capitalized.

Lastly for this article, the Get-Member and Get-Help cmdlets are invaluable. We've seen Get-Member above, which is useful when you can't remember a property or method name. Get-Help is what you call when you don't know how to use something. You can pass in the cmdlet as a parameter and get the help back. Get-Help call

Mike Fal has a good article on adding help to your own cmdlets, and you should do that.

That's enough to get your oriented, let's make one practical little piece of code.

Our First Useful Script

While we wrote a script above, it wasn't very useful or helpful. It's good practice, but it doesn't serve much of a purpose. Instead, let's do something useful.

If I run Get-Service, I get a long list of services. On most Windows machines, this is dozens of things running, most of which I don't care about. However, because the output of this can be saved into a variable, we can do things with it.

For example, I may run this:

$service = Get-Service

Now I have all the services on my machine in the variable with their name, status, etc. One useful cmdlet that I often use is Where-Object, which is like a WHERE clause in T-SQL. With this, I can filter things out. I'll show the code, then explain it.

$service | Where-Object {$_.Name -like "SQL*"}

I am using a pipe here, the | symbol. This essentially passes one object as a parameter to another cmdlet. In this case, I pass the $service object to Where-Object. My filtering takes place in the braces, {}, where I can specify how I filter. Here I want to like for a wildcard match, like, with a string. As with T-SQL, an asterisk is use for any number of characters.

The notation, $_., is used to specify the object passed in. In this case, $service is used for $_. The .Name specifies the property I want to do a comparison on. In this case, the name property. When I do this, I get just those services that start with SQL in the name. I could use the description, status, or other properties if I wished.

Since I'm using a pipe here, I can actually skip the variable and pipe the output from one cmdlet to another. Let me create a file that looks like this:

script file to get SQL services

Now I can save this and run it from the PoSh CLI. I do that and I just see my SQL Server services.

Running the Get Services script

While this might not feel super useful, being able to run this script and get my service status from the cmdline, without filtering things out manually or scrolling through the Services applet is handy.

Note: The comparison operators are very un-intuitive in PoSh. Instead of the <>=, or ==, we have -gt, -lt, -eq, and -ne. In another article, I'll cover more of these.

Summary

This article is a very basic introduction to PowerShell. We looked at variables as objects, how to run code, and build a short script. This gives you a starting point for working with the language in general. In the next article, I'll look at more SQL Server specific tasks.

I know that this covered a lot of information, and it can be confusing. I found that I was moving in circles sometimes when learning PoSh, getting fragments of what I needed, and not understanding some of what I learned, but I slowly worked my way through and practiced a bit, trying to build useful scripts for myself.

Follow along, try out some code, and let me know what's confusing, or what you want to learn.

 

Rate

5 (6)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (6)

You rated this post out of 5. Change rating