SQL Server

PowerShell Basics for DBAs

As the DB field gets more mature and as DBAs become more seasoned, so do companies. So companies are starting to keep more and more data, which in turn means more and more servers, which in turn means that DBAs can no longer manage things the way they used to. For example, it’s no longer acceptable to run through your morning checklist to make sure that servers are behaving and that backups are ok. This is where PowerShell comes into play. DBAs can now not only manage multiple servers at once, but they can do it very easily with often times very little coding.

So let’s get into the basics of PowerShell and see what it’s all about. By the way, if you’d like to know why you should even care about PowerShell, you can read about some of the things it can do for you in my article, Why Should DBAs Care About PowerShell?

Familiar concepts

PowerShell is easy because it presents you with things as drives. For example, if you wanted to access your C-drive you would use “CD C:” in PowerShell, just the same as you would in DOS. However, if you wanted to access your registry as well, you would also access it like a drive by typing “CD HKLM:” for example. Or if you wanted to access SQL Server via PowerShell, you’d type “CD SQLSERVER:”. So you can see that the concept is very simple… everything can be accessed as a drive.

Now, let’s say that you want to connect to a SQL Server, and to a specific DB. You’d simply type:

Sponsored Content

Passwords Haven’t Disappeared Yet

123456. Qwerty. Iloveyou. No, these are not exercises for people who are brand new to typing. Shockingly, they are among the most common passwords that end users choose in 2021. Research has found that the average business user must manually type out, or copy/paste, the credentials to 154 websites per month. We repeatedly got one question that surprised us: “Why would I ever trust a third party with control of my network?

>cd SQLServer:\SQL\ServerName\Default\Databases\DBName

And with everything exposed as a drive path, you already understand the structure of everything you’ll do in PowerShell. It also makes it really easy to switch between servers, or databases, or any other objects because all you have to do is replace the name of the object and leave the rest the same.

Now that I’ve explained how PowerShell is organized let’s take a quick look at how you get information about the different drives you can work with. The command for finding drives presented to you is ‘psdrive’ and you type it at the PowerShell prompt like this:

>psdrive

And when you do, you’ll get output that looks something like this:

PowerShell commands: psdrive

Of course, your output will look slightly different depending on what you have loaded on your box, but a lot of it will be the same too. So looking around here you can see several drives listed.  I’ll go through some of the more interesting ones:

  • Variable – here you can see all the built-in and user-defined variables for your active session.
  • Function — here you can see all the built-in and user-defined functions for your active session.
  • Env – here you can see all the environment variables and values for your box.
  • HKCU – HKey_Current_User registry hive.
  • HKLM – Hkey_Local_Machine registry hive.

And you can work with each of these just like you would the local file system.

Another major aspect of PowerShell is the cmdlet. These are the same as commands in DOS only they’re much easier to work with. The thing that makes them so much easier is their consistency. Cmdlets are always in the verb-noun format… always. Some good examples of cmdlets are get-service, out-file, get-process, format-table, and get-content. In the coming articles these cmdlets (and more) are going to become your best friends.

To use a cmdlet you have simply to type the name of it at the prompt and pass it whatever parameters it needs. Sometimes a cmdlet doesn’t need any parameters to give you a default output. Take get-service for example. It works all by itself. So if you type get-service at the prompt like this:

>get-service

You’ll get output that looks like this:

PowerShell commands: cmdlets

But what if you wanted to pass it a parameter? That’s the other thing that makes cmdlets so easy to work with. They all accept parameters in the exact same way. To pass a parameter to a cmdlet simply type “-“ followed by the parameter name, then a space and the value you want to pass in. Here’s an example using get-service:

>get-service –computername Server1

See, no more wondering if you have to use a dash or a slash, and no more wondering if you have to put a space between the parameter or not. They all work the same. Of course, for all string inputs you’re welcome to use double or single quotes if there aren’t any spaces in the string it isn’t necessary. All cmdlets handle errors in the same way too. Each one has a set of common parameters that they all support: debug, errorAction, ErrorVariable, and WarningAction, just to name a few. This means that error handling and debugging are built into every cmdlet so functionality will be the same across the board no matter if you’re working with SQL Server, IIS, Exchange, or anything else. It’s all the same.

Ok, we’ve covered a couple essentials of PowerShell this time, and next time in “Finding What You Need in PowerShell“, we’ll cover help, get-member, and maybe even start on format-table if there’s time.

Related Topics:

Don't leave your business open to attack! Come learn how to protect your AD in this FREE masterclass!REGISTER NOW - Thursday, December 2, 2021 @ 1 pm ET

Active Directory (AD) is leveraged by over 90% of enterprises worldwide as the authentication and authorization hub of their IT infrastructure—but its inherent complexity leaves it prone to misconfigurations that can allow attackers to slip into your network and wreak havoc. 

Join this session with Microsoft MVP and MCT Sander Berkouwer, who will explore:

  • Whether you should upgrade your domain controllers to Windows Server
    2019 and beyond
  • Achieving mission impossible: updating DCs within 48 hours
  • How to disable legacy protocols and outdated compatibility options in
    Active Directory

Sponsored by: