Introduction to Windows PowerShell for the SQL Server DBA Part 1

By:   |   Comments (2)   |   Related: 1 | 2 | 3 | More > PowerShell

I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.
Windows PowerShell is an extensible command-line shell and associated scripting language built on top of the .NET Framework v2.0. It was released in 2006 and is currently available for Windows XP SP2/SP3, Windows Server 2003, Windows Vista and is included in Windows Server 2008. PowerShell will be included as a common engineering criteria (CEC) in future releases of Microsoft server products making it a must to learn how to use it. Administrators - and DBAs included - have been using scripting to automate administrative tasks with scripting languages like DOS batch, VBScript, Perl, and a few third-party tools like KiXtart and WinScript. Windows PowerShell complements the administrators' scripting toolkit to easily manage and administer Windows workstations and servers and other Microsoft server products as they are being built using the .NET Framework. Although it is designed for operating systems, Windows PowerShell can be used to administer SQL Server 2005 instances and higher as Server Management Objects - the object model used to manage SQL Server 2005 - are built using the .NET Framework, thus, exposing the object model in PowerShell. In fact, SQL Server 2008 ships with its own PowerShell snap-in.

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.

powershell command prompt

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

powershell get-psdrive

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.

powershell dir

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

powershell get-psdrive

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

powershell get-command

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

powershell get-alias

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento 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

Comments For This Article

Sunday, February 22, 2009 - 5:44:55 PM - bass_player Back To Top (2808)

There's a ton of ways to use PowerShell.  In my previous life as a systems administrator, I have used it for automation tasks like extracting Active Directory users that have passwords that are about to expire and email it to them. It's just one of the examples. In fact, I have been translating a few of my VBScript scripts to PowerShell. What's nice about it is its flexibility and ease of use. Feel free to post any ideas that you may have and we can probably write a tip on how to use PowerShell with it.  To start with, check out the Microsoft TechNet site on Windows PowerShell

Friday, February 20, 2009 - 3:12:11 PM - dagenester Back To Top (2806)

I have been enjoying learning little tidbits about PS for a while now - since its Monad Shell days.  I appreciate any little tidbits that I can pick up with this semi-complex yet super logical Scripting Language.  Thanks for the help.