In order to be good at working with SQL Server and PowerShell, knowing how to explore and discover cmdlets, snapins, and modules is necessary.
Launch PowerShell ISE as administrator. If you prefer the console, you can also launch that instead, but ensure you are running it as administrator.
In this recipe, we will explore SQL-related cmdlets and modules:
To find out how many SQL-related cmdlets are in your system, type the following in your PowerShell editor and run:
#how many commands from modules that #have SQL in the name (Get-Command -Module "*SQL*" –CommandType Cmdlet).Count
To list the SQL-related cmdlets, type the following in your PowerShell editor and run:
#list all the SQL-related commands Get-Command -Module "*SQL*" –CommandType Cmdlet | Select-Object CommandType, Name, ModuleName | Sort-Object -Property ModuleName, CommandType, Name | Format-Table –AutoSize
To see which of these modules are loaded in your PowerShell session, type the following in your editor and run:
Get-Module -Name "*SQL*"
If you have already used any of the cmdlets in the previous step, then you should see both
SQLPSandSQLASCMDLETS. Otherwise, you will need to load these modules before you can use them.To explicitly load these modules, type the following and run:
Import-Module -Name "SQLPS"
Note that SQLASCMDLETS will be loaded when you load SQLPS.
At the core of PowerShell, we have cmdlets. A cmdlet (pronounced commandlet) is defined in MSDN as lightweight command that is used in the Windows PowerShell environment. It can be a compiled, reusable .NET code or an advanced function, or it can be a workflow that typically performs a very specific task. All cmdlets follow the verb-noun naming notation.
PowerShell ships with many cmdlets. In addition, many applications now also ship with their own cmdlets. For example, SharePoint has a fair number of PowerShell cmdlets that help with installation, configuration, and administration of the farm, sites, and everything in between. A list of cmdlets for SharePoint 2013 can be found at https://technet.microsoft.com/en-us/library/ff678226.aspx.
A legacy way of extending PowerShell is by registering additional snapins. A Snapin is a binary, or a DLL, that can contain a cmdlet. You can create your own snapin by building your own .NET source, compiling, and registering the snapin. You will always need to register snapins before you can use them. Snapins are a popular way of extending PowerShell.
The following table summarizes common tasks with snapins:
|
Task |
Syntax |
|---|---|
|
List loaded Snapins |
|
|
List Installed Snapins |
|
|
Show commands in a Snapin |
|
|
Load a specific Snapin |
|
Since PowerShell v2, modules are introduced as the improved and preferred method of extending PowerShell. A module is a package that can contain cmdlets, providers, functions, variables, and aliases. In PowerShell v2, modules are not loaded by default, so required modules need to be explicitly imported.
Common tasks with modules are summarized in the following table:
|
Task |
Syntax |
|---|---|
|
List loaded Modules |
|
|
List Installed Modules |
|
|
Show commands in a Module |
|
|
Load a specific Module |
|
One of the improved features of PowerShell v3 onwards is support for autoloading modules. You do not need to always explicitly load modules before using the contained cmdlets. Using the cmdlets in your script is enough to trigger PowerShell to load the module that contains it.
SQL Server 2014 modules are located at PowerShell | Modules in the install directory.
You can get a list of SQLPS and SQLASCMDLETS by running the following command:
Get-Command -CommandType Cmdlet -Module SQLPS,SQLASCMDLETS| Select-Object Name, Module | Sort-Object Module, Name | Format-Table -AutoSize
Here's the list of cmdlets as of this version of SQL Server 2014:
CommandType Name ModuleName
----------- ---- ----------
Cmdlet Add-RoleMember SQLASCMDLETS
Cmdlet Backup-ASDatabase SQLASCMDLETS
Cmdlet Invoke-ASCmd SQLASCMDLETS
Cmdlet Invoke-ProcessCube SQLASCMDLETS
Cmdlet Invoke-ProcessDimension SQLASCMDLETS
Cmdlet Invoke-ProcessPartition SQLASCMDLETS
Cmdlet Merge-Partition SQLASCMDLETS
Cmdlet New-RestoreFolder SQLASCMDLETS
Cmdlet New-RestoreLocation SQLASCMDLETS
Cmdlet Remove-RoleMember SQLASCMDLETS
Cmdlet Restore-ASDatabase SQLASCMDLETS
Cmdlet Add-SqlAvailabilityDatabase SQLPS
Cmdlet Add-SqlAvailabilityGroupListenerStaticIp SQLPS
Cmdlet Add-SqlFirewallRule SQLPS
Cmdlet Backup-SqlDatabase SQLPS
Cmdlet Convert-UrnToPath SQLPS
Cmdlet Decode-SqlName SQLPS
Cmdlet Disable-SqlAlwaysOn SQLPS
Cmdlet Enable-SqlAlwaysOn SQLPS
Cmdlet Encode-SqlName SQLPS
Cmdlet Get-SqlCredential SQLPS
Cmdlet Get-SqlDatabase SQLPS
Cmdlet Get-SqlInstance SQLPS
Cmdlet Get-SqlSmartAdmin SQLPS
Cmdlet Invoke-PolicyEvaluation SQLPS
Cmdlet Invoke-Sqlcmd SQLPS
Cmdlet Join-SqlAvailabilityGroup SQLPS
Cmdlet New-SqlAvailabilityGroup SQLPS
Cmdlet New-SqlAvailabilityGroupListener SQLPS
Cmdlet New-SqlAvailabilityReplica SQLPS
Cmdlet New-SqlBackupEncryptionOption SQLPS
Cmdlet New-SqlCredential SQLPS
Cmdlet New-SqlHADREndpoint SQLPS
Cmdlet Remove-SqlAvailabilityDatabase SQLPS
Cmdlet Remove-SqlAvailabilityGroup SQLPS
Cmdlet Remove-SqlAvailabilityReplica SQLPS
Cmdlet Remove-SqlCredential SQLPS
Cmdlet Remove-SqlFirewallRule SQLPS
Cmdlet Restore-SqlDatabase SQLPS
Cmdlet Resume-SqlAvailabilityDatabase SQLPS
Cmdlet Set-SqlAuthenticationMode SQLPS
Cmdlet Set-SqlAvailabilityGroup SQLPS
Cmdlet Set-SqlAvailabilityGroupListener SQLPS
Cmdlet Set-SqlAvailabilityReplica SQLPS
Cmdlet Set-SqlCredential SQLPS
Cmdlet Set-SqlHADREndpoint SQLPS
Cmdlet Set-SqlNetworkConfiguration SQLPS
Cmdlet Set-SqlSmartAdmin SQLPS
Cmdlet Start-SqlInstance SQLPS
Cmdlet Stop-SqlInstance SQLPS
Cmdlet Suspend-SqlAvailabilityDatabase SQLPS
Cmdlet Switch-SqlAvailabilityGroup SQLPS
Cmdlet Test-SqlAvailabilityGroup SQLPS
Cmdlet Test-SqlAvailabilityReplica SQLPS
Cmdlet Test-SqlDatabaseReplicaState SQLPS
Cmdlet Test-SqlSmartAdmin SQLPSTo learn more about these cmdlets, use the Get-Help cmdlet. For example, here's the command to learn more about Invoke-Sqlcmd:
Get-Help Invoke-Sqlcmd Get-Help Invoke-Sqlcmd -Detailed Get-Help Invoke-Sqlcmd -Examples Get-Help Invoke-Sqlcmd -Full
You can also check out the MSDN article on SQL Server Database Engine Cmdlets at http://msdn.microsoft.com/en-us/library/cc281847.aspx.
When you load the SQLPS module, several assemblies are loaded into your host.
To get a list of SQLServer-related assemblies loaded with the SQLPS module, use the following script, which will work in both PowerShell v2 and v3:
Import-Module SQLPS –DisableNameChecking
[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object {$_.FullName -match "SqlServer" } |
Select-Object FullNameIf you want to run on v3 or newer versions, you can take advantage of the simplified syntax:
Import-Module SQLPS –DisableNameChecking [AppDomain]::CurrentDomain.GetAssemblies() | Where-Object FullName -Match "SqlServer" | Select-Object FullName
This will show you all the loaded assemblies, including their public key tokens:
