Using PowerShell for SQL Server Admin

All SQL Server 2008 installations will automatically have PowerShell. As a SQL Server DBA you will not survive  without knowing PowerShell in future. For SQL Server there is a separate component of  PowerShel – SQLPS. You start SQLPS in SQL Server or by typing SQLPS in command prompt. SQLPS loads required components which you can work with SQL Server. In the normal PowerShell editor SQL Server related components are not loaded.

Starting SQLPS from SQL Server:

PowerShell for SQL Server


PowerShell for SQL Server

In SQLPS there is a directory structure which you can traverse through that using “cd” which is essentially an alias of Set-Location cmdlet. You can see the directories available in each level by executing “dir” which is another alias for the Get-ChildItem cmdlet.

PowerShell for SQL Server

In the root there are four logical drives or directories as you can see in the above screenshot.

  1. SQL
  2. SQLPolicy
  3. SQLRegistration
  4. DataCollection

The below screenshot shows that I have traversed through the directory structure upto “tables” object in my local server. Look at the prompt carefully.

PowerShell for SQL Server

Now if you execute “dir” you will get a list of tables available in the AdventureWorks database. You can execute T-SQL commands at this point. For example to get a list of the top 10 tables with the highest number of records.

gci | sort-object -Property RowCount -Desc | select-object schema,name -first 10 | format-table


Managing System Services using PowerShell

You can manage system services in local or remote computers using PowerShell. PowerShellprovides you set of cmdlets to work with system services. They are;

  • Get-Service
  • Stop-Service
  • Start-Service
  • Suspend-Service
  • Resume-Service
  • Restart-Service
  • Set-Service
  • New-Service

Let’s consider a simple scenario – you need to stop the SQLBrowser service running in a remote computer. The below statement above get the SQLBrowser service in remote computer, Susantha-lp and stores in a variable called “$s”

$s = Get-Service -Name SQLBrowser -ComputerName susantha-lp

The next step  is to stop the service.  There are two different methods available for doing this:

  1. You can use Stop-Service cmdlet or
  2. You can invoke stop() method in “$s” object

The first method of stopping the service can be achieved using the below code:

$s | Stop-Service

The second method is:


Both methods are really simple. My preference would be for the second method due to its object oriented approach.Continues…

Using PowerShell with Event Logs

Reading Event logs with PowerShell

An event log is a windows service that manages event logging in a computer. When this service is started, Windows logs important information about the operation of the system and the applications running on it . The logs available on a system depend on the system’s role and the services installed.

Two general types of log files are used;

  1. Windows log
  2. Application and services log

Event log records events of different categories. namely:

  • Information
  • Warning
  • Error
  • Critical
  • Audit success
  • Audit failures

The GUI Event Viewer   is used to view the individual  events in an event log. In addition to  the GUI tool, PowerShell can be used to query the event log. The following PowerShell cmdlets can be used to manage the event log:

  • Get-WinEvent
  • Get-EventLog
  • Clear-EventLog
  • Limit-EventLog
  • Show-EventLog

The below script  displays records from the event log which has an “error” state in the Application, System and Security logs.

PowerShell Read Event Logs

Parameter “-Newest 100” gives only latest 100 entries in event log. Continues…

PowerShell Tutorial – WMI Objects

Using WMI Objects in PowerShell

WMI, Windows Management Instrumentation is set of managed APIs provided by the Windows OS to expose information about local or remote computers.

You can use WMI objects from  PowerShell  by using the  Get-WMIObject cmdlet use to execute WMI objects as shown below

PowerShell WMI Objects

You can see the list of WMI objects available by executing Get-WMIObject -List as shown below:

PowerShell WMI Objects

More complex WMI queries such as the below example can be executed:

PowerShell WMI Objects

In the above query $QueryText is a variable which contains the WMI query which is similar to  a T-SQL style query.  This query extracts the logical disk information of C,D and G whereFreeSpace is greater than a certain value and the FileSystem is NTFS.

The below query gives the same result as above. Examine the way it has written, as it is a little different.

Get-WmiObject -Class
Win32_LogicalDisk | Where-Object {

 $_.DeviceID -eq "C:"
-or $_.DeviceID -eq "D:" -or $_.DeviceID -eq "G:"-and
$_.FreeSpace -gt 104857600 -and $_.FileSystem -eq "NTFS"} |

Personally I prefer the first version as it is more similar to T-SQL. Continues…

PowerShell Tutorial – CmdLets

In common with other scripting languages, in PowerShell   has basic language elements like Variables, Arrays, Functions, Objects, Loops, IF statements, Switch statements, etc. (See here for a full explanation)

PowerShell CmdLets (Commands)

In PowerShell  commands are known as CmdLets (pronounced Command Lets). Cmdlets follow the naming convention of Verb-Noun combination:

E.g: Get-Help, Get-Service, Get-Process

PowerShell cmdlets are not case sensitive and are the smallest unit of functionality in PS.  You can use either the PowerShell Integrated Script Environment shown below (just type powershell_ise into the search box in the Windows start menu to launch this) or the command line tool to execute Cmdlets.

PowerShell Tutorial

In the PowerShell ISE. you enter the command at the prompt and the results of the cmdlet will be displayed in the middle panel of the tool. You can use the top panel in the tool to write scripts and also to execute single Cmdlets. The F5 key executes the entire script while F8 key executes only highlighted Cmdlets (Run Selection).

PowerShell Tutorial

Cmdlets are simple to type, you can use Tab key to auto complete the Cmdlet. E.g: Type Get-Pro then press the Tab key.

Cmdlet parameters

Cmdlet accept  which are denoted by using the “-“symbol:

PowerShell Tutorial

For example, the “-Name” is the parameter instructs PowerShell to display only “winrm” service information. Parameters are  also  auto completed  using  the Tab key, so that you don’t have to remember the entire parameter name.


Piping or pipelining is a method of combining two or more PowerShell Cmdlets to do a single task. PowerShell is a fully objected oriented scripting language as a result Cmdlet   returns an object as result. To combine Powershell Cmdlets you can use the symbol, “|”.

PowerShell Tutorial

The Get-Service cmdlet returns all services (whatever the state) on the local machine. It returns as an object. The “|” or piping passes that object in to next cmdlet (where-object) which essentially does the filtering.  The braces “{ }” represents the body of the where-object cmdlet, whichs specifies a condition. “$_.Status” is the current object (“$_.”), property (“Status”) and “-eq” is the logical condition (ie “=”).
Operators   in PowerShell user characters and not as symbols as  in other languages.

The Where-object cmdlet iterates through all the objects returned from the Get-Service cmdlet and filters out only the objects which are have the status of “Running”.Continues…

PowerShell Remoting

A major drawback of PowerShell 1.0 was the lack of a method to execute commands on a remote machine. PowerShell 2.0 addresses this with a new feature named remoting, which is designed to enable command (or script) execution on remote machines. Using PowerShell remoting, commands can be issued either synchronously or asynchronously and even scheduled or throttled.

Before using PowerShell remoting, you will first need the appropriate permissions to connect to the remote machine, then execute PowerShell, and finally execute the desired command or scripts. Additionally, the remote machine will need to have both PowerShell 2.0 and Windows Remote Management (WinRM) installed, and PowerShell will need to be configured for remoting. Note that the commands executed via remoting will be subject to the remote machine’s execution policies, preferences, and profiles.

Powershell Remoting Requirements

Before using PowerShell remoting, both the local and remote computers must have the below:

  • PowerShell 2.0 or later
  • .NET Framework 2.0 or later
  • Windows Remote Management (WinRM) 2.0 (this is part of Windows 7 and Windows Server 2008 R2. For previous versions of Windows, an integrated installation package needs to be downloaded and installed – the PowerShell 2.0 download includes this.).

Configuring Remoting

On  Windows Server 2008 R2, both PowerShell and WinRM are installed by default, however for security reasons, both PowerShell remoting and WinRM are initially configured to not allow remote connections. There are several methods to configure remoting:

The simplest method to enable PowerShell remoting is to execute the Enable-PSRemoting cmdlet:
PS C:\> enable-pssremoting
Once this is executed, the below tasks are performed by the  cmdlet:

  • Runs the Set-WSManQuickConfig cmdlet, which in turn executes the belows  tasks:
    • Starts up the WinRM service.
    • Sets the WinRM service startup type on the  to Automatic.
    • Creates a listener to listen for and accept requests on an IP address.
    • Enables a firewall exception for WS-Management communications.
  • Enables all the registered  PowerShell session configurations to receive instructions from  remote computers.
  • Registers the “Microsoft.PowerShell” session configuration (unless it has  already been registered).
  • Registers the “Microsoft.PowerShell32” session configuration on 64-bit systems (unless it has  already been registered).
  • Removes  “Deny Everyone” setting from the security descriptor for all  registered session configurations.
  • Finally, restarts  WinRM  to make the above changes effective.

Note that the Enable-PSRemoting cmdlet needs to be executed as an Administrator (using the Run As Administrator option).

Using PowerShell Remoting

The power PowerShell remoting is that any the cmdlets/scripts you used in PowerShell 1.0 are available everywhere (provided PowerShell is installed on the server).

Automating IIS with PowerShell

As with most areas of Windows Server 2008 and 2008 R2 , Microsoft is emphasizing PowerShell as an important tool for managing IIS 7 and IIS 7.5. The IIS PowerShell snap-in provides many new cmdlets and enables admins to manage IIS properties in numerous different ways.

Select Windows PowerShell Modules from the Administrative Tools group and the system will load the modules included with Windows Server 2008 , including the WebAdministration module which provides the IIS functionality. You may also import the module manually from the Windows PowerShell prompt using the below command:

Import-Module WebAdministration

Once the IIS PowerShell snap-in is running, you can display all the cmdlets it contains using the below command:

Get-Command –pssnapin WebAdministration

The IIS PowerShell snap-in uses three types of cmdlets:

  • PowerShell provider cmdlets
  • Task-oriented cmdlets
  • Low-level configuration cmdlets

These cmdlet types relate to the three different methods of managing IIS from the   PowerShell prompt.

Using the IIS PowerShell Provider

The IIS PowerShell provider creates a hierarchical IIS namespace which admins can navigate similar to  a  standard directory structure. Type iis: and press Enter at the PowerShell prompt (with the WebAdministration module having been already imported) and the prompt changes to PS IIS:>  then typing the dir command displays, but the top level of the IIS namespace (not the file system) as below:


After moving to the Sites directory using  the cd Sites command, the dir command displays a list of the IIS sites on the server.

The Get-Item cmdlet allows you to show selected sites in the same format. By piping results of the Get-Item cmdlet to the Select-Object cmdlet, you can see all properties of a selected site.

Getting Started with PowerShell for SQL Server

By default SQL Server 2008  installs  PowerShell and two SQL Server PowerShell snap-ins which expose SQL Server functionality from PowerShell. PowerShell Snap-ins are simply .NET assemblies which contain Windows PowerShell providers and/or  PowerShell cmdlets that extend the functionality of the shell. When a snap-in is loaded into  PowerShell , its cmdlets and providers  are registered with the shell.

Starting PowerShell

PowerShell can be invoked and used on  SQL Server 2008 server in four ways:

  1. Using the sqlps utility
    The sqlps utility is a command line utility for executing PowerShell commands and  is installed by default in the  \Program Files\Microsoft SQL Server\100\Tools\Binn folder. Since the Binn folder is added to the System PATH variable, the sqlps utility can be launched by selecting Start > Run and then entering sqlps. This should launch the sql utility:
    sql server powershell
  2. PowerShell can also be launched from within  SSMS (SQL Server Management Studio) by right-clicking  an object in the Object Explorer, as shown below. SSMS will then launch launch  sqlps and set
    the location to the object  which you right clicked.
    sql server powershell
  3. PowerShell cmdlets and scripts  can also be launched from inside a SQL Server Agent job. A SQL Server Agent job step can be set to type PowerShell and then  PowerShell cmdlets and scripts can be used.
    powershell sql server
  4. Launch Windows PowerShell and then manually add the SQL Server PowerShell provider functionality. Launch PowerShell from the Start menu (Start > PowerShell) then execute the below commands:
    Get-PSSnapin –registered
    Add-PSSnapin SqlServerProviderSnapin100
    Add-PSSnapin SqlServerCmdletSnapin100

    powershell sql server

    The Get-PSSnapin –registered command simply shows the listing of the available snap-ins and the two Add commands registers the snap-ins.To confirm the snap-ins are added to your PowerShell session execute the command:

    Get-PSSnapin Sql*

Navigate The SQL Server Object Hierarchy using PowerShell

To see the SQLSERVER drive  along with other PowerShell drives which are available in the current environment, execute the cmdlet Get-PSDrive :

sql server powershell


PowerShell Tutorial – Loops (For, ForEach, While, Do-While, Do-Until)

Loops are an essential construct in any programming language as they enable a block of code to be repeatedly executed. This can be useful for iterating through items in an object or repeatedly performing an operation.

This tutorial covers the five types of loops that are provided in PowerShell – namely For, ForEach, While, Do-While, and Do-Until.

ForEach Loop

The foreach statement is very useful in PowerShell when you need  to loop through an array or loop through items in an object.

ForEach allows the loop to evaluate the number a certain type of objects in an array or parent object and loop through those objects. One thing to bear in mind is that a ForEach loop is that there is a performance penalty for this convenience.

The below example,  shows a ForEach Loop operating on a object, the code assigns the  object resulting from the Get-process cmdlet to a variable, and then iterates through the  process items in the object and displays the process name.

$Processes=get-process | select-object ProcessName
foreach ($process in $Processes)
{write-host $i "Process Name is " $process.Processname;$i++; }

Foreach can also   iterate through an array – the below code lists all the elements in an array, does a cumulative addition, and computes the total:

foreach ($element in in $testarray)
{ $j=$j+$element;
Write-host  "Cumulative Amount =" $j ;
$i++ }

For Loop

The For loop has three parts :