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


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