PowerShell for the SQL Server DBA Environment Setup


By:   |   Updated: 2021-10-29   |   Comments   |   Related: More > 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.
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.

powershell

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.

powershell

In the first line, write Write-Host "Hello World!".

powershell

Proceed to save the file and name it HelloWorld.ps1.

powershell

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

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

powershell

You can also execute the script like this:

powershell

Windows Command Prompt

The exact same thing applies for a Command Prompt (cmd) window:

powershell
powershell

Run with PowerShell

You can locate the script within the filesystem, right-click on it and select "Run with PowerShell".

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.

powershell

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".

powershell

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.

powershell

The below error shows that running scripts is disabled.

powershell

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.

powershell

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





get scripts

next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips


Article Last Updated: 2021-10-29

Comments For This Article





download














get free sql tips
agree to terms