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

   

   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 :

The above output shows that a new drive ( SQLSERVER:\. ) is available which is the root drive.To navigate to it and and query the child items use the below command:

Set-Location SQLSERVER:
Get-ChildItem | Select-Object Name

The above output shows the following child items:

• SQL
• SQLPolicy
• SQLRegistration
• DataCollection

To navigate to a subfolder, use the cmdlet Set-Location, as shown below:

Set-Location SQL
Get-ChildItem

Each folder in the hierarchy contains different methods which can be invoked and different properties you can access and set values to. To find all the methods and properties available in a folder, use the Get-item and Get-member cmdlets as shown below:

Set-Location SQLSERVER:\SQL
Set-Location machine_name\DEFAULT\Databases
Get-Item . | Get-Member -Type Methods

In addition, you can get server information about the default instance using the Get-Item cmdlet and its properties using the below code:

Set-Location SQLSERVER:\SQL\machine_name\
$server = Get-Item Default
$server.get_VersionString()
$Server.Settings.Properties | Select-Object Name, Value | Format-Table –auto

The Invoke-Sqlcmd Cmdlet

The SQL Server PowerShell provider includes the cmdlet Invoke-Sqlcmd which is extremely useful for executing ad hoc queries and stored procedures.

For example, to query all the data from the Address table in AdventureWorks2008 execute the below code:

Set-Location SQLSERVER:\SQL\machine_name\DEFAULT\Databases\AdventureWorks2008
Invoke-Sqlcmd -Query "select top 20 * from Person.Address" | format-table –Auto

To get all the methods and properties available for an object returned from Invoke-Sqlcmd use the Get-Member cmdlet. The below example uses the parameter –ServerInstance with the cmdlet Get-Item. In the below code (Get-Item .) gets the current server instance name .

Set-Location SQLSERVER:\SQL\machine_name\DEFAULT\
$myTable =Invoke-Sqlcmd -Query "SELECT * from master.dbo.sysobjects " -ServerInstance
(Get-Item .)
$mMyTtable | get-member

You can access the row information using the Foreach-Object cmdlet as in the below code sample:

$myTable =Invoke-Sqlcmd -Query "SELECT * from master.dbo.sysdatabases "
-ServerInstance (Get-Item .)
$Mytable| Foreach-Object {$_.db_id;$_.name;$_.crdate;} | Format-Table -Auto

Using SQL Server Management Objects with PowerShell

You can leverage the SQL Server SMO class library via PowerShell to connect to SQL Server and retrieve and manipulate data.

Before using SMO objects, you will need to load the SMO class library (ie Microsoft.SqlServer.Smo.dll).   The LoadWithPartialName method can load an assembly from the application directory or from the GAC using a partial name. You can then connect to a SQL Server instance using SQL Server SMO using the below code:

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) |
Out-Null
$smovar = New-Object (’Microsoft.SqlServer.Management.Smo.Server’)
‘machine_name\SQL2008’

Using SMO you can connect to a database and perform any operations that you would normally perform in SSMS. As a simple example the below commands create the database “dummydb” on the SQL Server default instance using SMO:

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("machine_name")
$database = New-Object Microsoft.SqlServer.Management.Smo.Database($server,"dummydb")
$database.create()