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:

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