PowerShell Basics for DBAs
Familiar conceptsPowerShell 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:
- 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.
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.
You’ll get output that looks like this:
>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.