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?
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:
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:
And when you do, you’ll get output that looks something like this:
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:
You’ll get output that looks like this:
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.
More in SQL Server
How to Install SQL Server 2022
Jun 24, 2022 | Michael Reinders
Microsoft Introduces SQL Server Information Protection Sensitivity Labels
Apr 19, 2022 | Rabia Noureen
Microsoft Gives Up on Running SQL Server in a Windows Container
Jul 5, 2021 | Brad Sams
New High Availability and Disaster Recovery Features in SQL Server 2019
Nov 20, 2019 | Michael Otey
Ensuring Disaster Recoverability for Your Legacy SQL Server 2008/R2
Jul 22, 2019 | Michael Otey
Moving SQL Databases to the Cloud with the Data Migration Assistant
Jun 12, 2019 | Scott Allen
Most popular on petri