Getting Started with SQL Server Powershell

sqlps utility starts a Windows PowerShell  session with the SQL Server PowerShell provider and cmdlets loaded and registered.

You can enter PowerShell commands or scripts that use the SQL Server PowerShell components to work with instances of SQL Server and their objects.

You can use the sqlps utility to do the following:
  • Interactively run PowerShell commands.
  • Run PowerShell script files.
  • Run SQL Server cmdlets.
  • Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.

To start sqlps utility : -

To start sqlps utility : -

Through Windows PowerShell ,we can interact with SQL Server components by importing the sqlps module into Windows PowerShell environment .

The sqlps module loads in the SQL Server PowerShell provider and cmdlets, and the SQL Server Management Object (SMO) assemblies used by the provider and cmdlets. 

Powershell Commands:

Get-Module -listAvailable

##setting execution policy
Set-ExecutionPolicy unrestricted

## Importing  the SQLPS Module with -DisableNameChecking to suppress warnings
Import-Module “sqlps” -DisableNameChecking

Get-Command –module "sqlps"

Get-PSSnapin -registered

##if not added,to add sql server snapins
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

##to get commands in a snapin
Get-Command -pssnapin SqlServerCmdletSnapin100
Get-Command -pssnapin SqlServerProviderSnapin100

References :

For more,detailed & accurate Informations:

sqlps utility -

To Install  - Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 -

To Import SQLPS module into  Windows PowerShell environment -

To load only the SMO assemblies without the provider or cmdlets -

SMO object model diagram -

To navigate a hierarchy of SQL Server objects using SQL Server provider -

To navigate SQL Server PowerShell Paths -

Working with SQL Server PowerShell Paths -

No comments: