Introduction to Windows PowerShell for the SQL Server DBA Part 1

By: Edwin Sarmiento   |   Updated: 2009-02-04   |   Comments (2)   |   Related: 1 | 2 | 3 | > PowerShell

Problem

Solution

This series of articles will be dealing with introducing Windows PowerShell, its language semantics and how we can write scripts. Once we have a good grasp of Windows PowerShell, we will progress with using it to administer SQL Server.

Depending on the version that you would like to work with, you can download Windows PowerShell from the Microsoft Download Center. The publicly available released version of PowerShell is v1.0 and this is the same version that gets installed along with SQL Server 2008 (v2.0 is available as a community technology preview CTP and is the version that I am currently using). Make sure you already have Microsoft .NET Framework v2.0 (and SQL Server 2005 client tools for managing SQL Server with PowerShell) installed with the latest service packs on your machine before installing Windows PowerShell. After completing the installation, you are now ready to launch Windows PowerShell. You can do this by running the powershell.exe executable from either your command-line or from the Run command. This will open up a command shell similar to DOS, but with a PS prompt before each line.

You might notice that the font color might be different from the usual DOS environment that you're familiar with as I have configured the PowerShell environment differently so as not to get confused between the two.

Navigating the system using the PowerShell command-line

The best way to learn Windows PowerShell is to translate the common tasks that we do using our command-line like listing files and file system properties. PowerShell provides access to these as well as other Windows resources like the registry but with a twist - it exposes these additional resources so that they look like drives, giving you a similar approach when working with them. To display all of the resources in your system, you can use the Get-PSDrive cmdlet

Notice that the drives are not just limited to letters. HKLM, for example, maps to the HKEY_LOCAL_MACHINE portion of the registry. To illustrate, we can navigate to the SQL Server registry key using the usual cd and dir command we use in DOS and list all of the subkeys under that.

You can explore all of the resources available as exposed by PowerShell by just using those familiar DOS commands.

Using as much lifeline as we can get

Being new to the environment, we need all the help we can get to make the most out of our learning experience. The most important cmdlet that we can ever learn from Windows PowerShell is the Get-Help cmdlet. This displays help about Windows PowerShell cmdlets and concepts. If you want to know what a particular cmdlet does, simply pass that as a parameter to the Get-Help cmdlet as shown in the example below. Let's say we want to know what the Get-PSDrive cmdlet does

Get-Help is just one of the cmdlets available in Windows PowerShell. But how do we know what else is available for us? You can use the Get-Command cmdlet to display a list of all the Windows PowerShell cmdlets

These two cmdlets will provide most, if not all, of the help we will need when working with Windows PowerShell. But, that's not all. In case you have forgotten about the exact name of the cmdlet but remember the first letter of the second part of the name, you can use the Tab key to use the auto-completion feature similar to how it works with DOS. Let's say you would like to display a list of cmdlets that start with Get-H, you can type the first part and use the Tab key to search for what you're looking for. This will display cmdlets such as Get-Help, Get-History and Get-Host..

So, what exactly is a cmdlet?

We have been talking about and using cmdlets in our examples but have not really defined what a cmdlet is. Cmdlets (pronounced as command-let) are miniature commands similar to those of command-line tools that perform specific tasks inside Windows PowerShell. They are named using a standard naming convention with the verb-noun format. This naming standard makes it easy to "guess" a cmdlet that performs a specific task, like the cmdlet to retrieve all the process running in Windows is named Get-Process. You can use the built-in PowerShell cmdlets or create your own that can be installed to extend its functionalities, similar to what Exchange Server 2007 and SQL Server 2008 have done.

Aliases

Aliases are alternative names assigned to a cmdlet which make it easy for us to type a cmdlet without entering its full name. PowerShell has its own list of built-in aliases, like the one we already used - dir and cd. The dir command (together with the ls alias which is similar to that of Unix) and cd command are aliases for the Get-ChildItem and Set-Location cmdlets, respectively. To list all of the available built-in aliases, run the Get-Alias cmdlet

You can also create your own aliases if you want to call other cmdlets a different name or maybe define your own functions and call them using your user-defined aliases.

Next Steps

You have just scratched the tip of the iceberg with Windows PowerShell. Throughout this series, we will be exploring the power of Windows PowerShell and will progress to write a simple PowerShell script to manage SQL Server

• Download and install Windows PowerShell
• Check out the Windows PowerShell Owner's Manual from Microsoft TechNet

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips