By: Alejandro Cobar | Updated: 2021-10-29 | Comments | Related: > PowerShell
Problem
PowerShell is a great tool that can be used by data professionals to perform lots of different tasks. Mostly SQL Server Management Studio is used to manage and interact with SQL Server, but this series we will discuss how to get started using PowerShell to take your administration to the next level.
Solution
In this article, we will learn how to setup PowerShell, along with simple steps to create and execute our very first script.
Initial considerations
A very basic knowledge of coding in any other programming language will be assumed. To perform any tests, please make sure to use a sandbox environment. No interaction with SQL Server will be covered in this article, that will be covered in future articles.
What to do first?
Well, a classic answer to this one, it depends. In January 2018 PowerShell Core was made available for Linux and MacOS systems (unthinkable back then if you ask me), something that was only available in Windows prior to that. Therefore, depending on the environment you feel the most comfortable working with, you could start writing your scripts in Windows, Linux or MacOS.
Setting up PowerShell in the platform of your choosing
PowerShell on Linux
Here's an article on how to install PowerShell on Linux, so you can follow that guideline to setup your environment.
PowerShell on MacOS
Since there's currently no content on MSSQLTips.com to address the installation of PowerShell on MacOS, here is a link to installing PowerShell on MacOS documentation from Microsoft that gives you a guideline.
PowerShell on Windows
For Windows, it's relatively easy to start with PowerShell because, guess what, in Windows 10 / Windows Server (I think this started back with Windows 7 SP1 / Windows Server 2008 R2 SP1) PowerShell is included by default. There are a few things I'd like to address though.
If you want to double-check that PowerShell is installed in your Windows environment, then do the following:
- Click on the Windows taskbar button.
- Type PowerShell.
Click on the item that says "Windows PowerShell".
In the window that pops, type Get-Host and hit "Enter" as shown
below. This command will show what version of PowerShell is installed.
If for whatever reason you'd like to use a different version of PowerShell (i.e. PowerShell 7), check this Microsoft guide for installing other PowerShell versions.
As you may have noticed, when you typed "PowerShell" in the Windows taskbar, you also got something called "PowerShell ISE", which stands for "Integrated Scripting Environment". I personally like to develop my scripts with the ISE, but you can use the tool that you feel the most comfortable with (you can even use Notepad to write your scripts if you really wanted to).
Note about PowerShell on Linux and MacOS
It is very important to mention that the Linux and MacOS variants might not be able to do certain things, as opposed to the Windows counterpart. I'm not going to dive into the specifics because what I'll cover in the articles should be achievable from any platform. Regardless, once you master the language, have a self-taught spirit going on, and know exactly what you want to accomplish, then you should be able to figure out when something isn't achievable in a specific platform.
Writing the first script – the classic "Hello World!"
With PowerShell setup on your platform of choice, let's get our hands dirty. From this point forward, I will be referring to all my examples from a Windows perspective, and the reason is because I don't want to exclude anyone who is still supporting legacy or Windows only SQL Server instances. Let's remember that prior to SQL Server 2017, you could only use SQL Server in a Windows environment exclusively.
With that out of the way, let's begin.
Open up your favorite editor and create an empty new file. In my case, I will be using PowerShell ISE, so you're most welcome to follow along with that or something different.
In the first line, write Write-Host "Hello World!".
Proceed to save the file and name it HelloWorld.ps1.
How to run your first script?
There are different ways you can run your script, let me show you them.
PowerShell ISE
If you're using PowerShell ISE, then you can simply click the run button (the one with the green arrow) and you will see the output in the console window at the bottom.
PowerShell Console
You can open this as we did above when we did a search for PowerShell.
Use the CD command to move to the folder where you saved the script file and
execute it like this .\HelloWorld.ps1
You can also execute the script like this:
Windows Command Prompt
The exact same thing applies for a Command Prompt (cmd) window:
Run with PowerShell
You can locate the script within the filesystem, right-click on it and select "Run with PowerShell".
Keep in mind that if you do it this way, a PowerShell window will open, the script will execute, and the window will close after the script finishes. To avoid this behavior (which is by default for this particular way of executing PowerShell scripts), you would need to modify the registry, which I don't recommend, but it is an option that exists regardless.
Run with Task Scheduler
You can use "Task Scheduler" in Windows to schedule the execution of a PowerShell script.
Run with SQL Agent Job
From a SQL Server instance that has SQL Agent up and running, you can create a job that executes a PowerShell script. I found an article written by Tim Smith where he mentions this option, so take a look at that if you're interested.
Things that could go wrong
I'll list a few cases that might result in an unsatisfactory outcome for you.
Errors in your script
This is what an error in your script might look like. On purpose, I've written "Write-HostX" instead of "Write-Host".
Issues with the current PowerShell Execution Policy
The PowerShell Execution Policy is the setting that determines which scripts can be executed in the system. By default, this is set to "Restricted", which would prevent you (or anybody else) from executing a script and it looks something like this.
The below error shows that running scripts is disabled.
To change the Execution Policy, you (or somebody with the appropriate privileges)
has to execute the command Set-ExecutionPolicy RemoteSigned.
Note: I previously did this, so that's why my first demonstration worked without any issues.
I'll leave you with Microsoft's official documentation around Execution Policies in case you'd like to take a deeper dive.
Next Steps
- If by the end of this article you were able to create and run your first PowerShell "HelloWorld" script, then congratulations!
- In the next article, I'm going to be covering the fundamentals of the language: from the creation and scope of variables and their data types, if/else statements, for – foreach - while loops, arrays, try/catch blocks, how to use comments, among other things, so stay tuned!
- Check out this previous article: PowerShell Commands for SQL Server – Getting Started
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-10-29
