We all know by now that SQL Server 2012 RTM is available for download.
Prior to SQL Server 2012, Microsoft provided a mini shell called sqlps.exe. In SQL Server 2012, SQLPS exists in the form of module. SQLPS.exe still exists in SQL Server 2012, however, it basically does a module call. This article illustrates how to make use of the SQLPS module and how to access SQL Server components and features via Windows PowerShell.
Note: You can download from the website http://www.microsoft.com/sqlserver.
Pre-Requisite
In order to take advantage of SQLPS the following are needed:
a. PowerShell 2.0: It is part of the Windows 2008 install and you can enable it from the Windows server features.
b. SQLPS module: It is part of the SQL Server 2012 install. Once installed, the module will be visible under Powershell. After the install, all you need is to import the SQLPS module.
c. Execution-Policy: Powershell’s Execution policy should not be Unrestricted.
Step 1
Let's launch PowerShell using RunAS Administrator from the Windows Start menu, as shown below.
Launch PowerShell using RunAS Administrator
This opens a new PowerShell window session as shown.
New PowerShell window session
Step 2
Now, let's set the execution policy to Un-Restricted as shown below.
Set the execution policy to Un-Restricted
Step 3
List all of the modules available on the PowerShell environment.
Get-Module –ListAvailable
ModuleType Name ExportedCommands
Manifest ADRMS {}
Manifest AppLocker {}
Manifest BestPractices {}
Manifest BitsTransfer {}
Manifest PSDiagnostics {}
Manifest ServerManager {}
Manifest TroubleshootingPack {}
Manifest SQLASCMDLETS {}
Manifest SQLPS {}
Get-Module –ListAvailable
If you do not see SQLPS in the list of modules available, install the SQL Server 2012 PowerShell extensions from the feature pack. http://www.microsoft.com/download/en/details.aspx?id=29065
Step 4
Import the SQLPS module as shown below. This will make all of the SQLPS related cmdlets available on the current PowerShell session.
Import-Module SQLPS
Import-Module SQLPS
You can suppress the WARNING message using a parameter “DisableNameChecking”.
Import-Module sqlps –DisableNameChecking
Step 5
You can list the number of commands available in the SQLPS module by running the following cmdlet.
Get-Command -CommandType cmdlet -Module sqlps | group-object -Property verb
Count Name Group
2 Add {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp}
1 Backup {Backup-SqlDatabase}
1 Convert {Convert-UrnToPath}
1 Decode {Decode-SqlName}
1 Disable {Disable-SqlAlwaysOn}
1 Enable {Enable-SqlAlwaysOn}
1 Encode {Encode-SqlName}
2 Invoke {Invoke-PolicyEvaluation, Invoke-Sqlcmd}
1 Join {Join-SqlAvailabilityGroup}
4 New {New-SqlAvailabilityGroup, SqlAvailabilityRepl...
3 Remove {Remove-SqlAvailabilityDatabase, Remove-SqlAvailabilityGroup….
1 Restore {Restore-SqlDatabase}
1 Resume {Resume-SqlAvailabilityDatabase}
4 Set {Set-SqlAvailabilityGroup, Set-SqlAvailabilityGroupListener…
1 Suspend {Suspend-SqlAvailabilityDatabase}
1 Switch {Switch-SqlAvailabilityGroup}
3 Test {Test-SqlAvailabilityGroup, Test-SqlAvailabilityReplica…
Get-Command -CommandType cmdlet -Module sqlps | group-object -Property verb
Step 6
List all of the cmdlets and functions available under the module SQLPS.
Get-command –module SQLPS
You can see the following cmdlets listed.
Add-SqlAvailabilityDatabase
Add-SqlAvailabilityGroupListenerStaticIp
Backup-SqlDatabase
Convert-UrnToPath
Decode-SqlName
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Encode-SqlName
Invoke-PolicyEvaluation
Invoke-Sqlcmd
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityGroupListener
New-SqlAvailabilityReplica
New-SqlHADREndpoint
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityReplica
Restore-SqlDatabase
Resume-SqlAvailabilityDatabase
Set-SqlAvailabilityGroup
Set-SqlAvailabilityGroupListener
Set-SqlAvailabilityReplica
Set-SqlHADREndpoint
SQLSERVER:
Suspend-SqlAvailabilityDatabase
Switch-SqlAvailabilityGroup
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
After importing the SQLPS module, SQL Server will be available to be navigated like a file system. We could navigate SQL Server simply by using CD (Set-Location) and DIR (Get-ChildItem) commands.
Example:
CD SQLSERVER:
DIR
Name Root Description
---- ---- -----------
SQL SQLSERVER:\SQL SQL Server Database Engine
SQLPolicy SQLSERVER:\SQLPolicy SQL Server Policy Management
SQLRegistration SQLSERVER:\SQLRegistration SQL Server Registrations
DataCollection SQLSERVER:\DataCollection SQL Server Data Collection
XEvent SQLSERVER:\XEvent SQL Server Extended Events
Utility SQLSERVER:\Utility SQL Server Utility
DAC SQLSERVER:\DAC SQL Server Data-Tier Application Component
SSIS SQLSERVER:\SSIS SQL Server Integration Services
SQLAS SQLSERVER:\SQLAS SQL Server Analysis Services
Navigate SQL Server by using CD and DIR
Note: Here USTESTSQL01 is the hostname and SQLENG is the instance name. {Named Instance}
We can list all of the available folders under the instance by running the command DIR.
DIR
Audits
AvailabilityGroups
BackupDevices
Credentials
CryptographicProviders
Databases
Endpoints
JobServer
Languages
LinkedServers
Logins
Mail
ResourceGovernor
Roles
ServerAuditSpecifications
SystemDataTypes
SystemMessages
Triggers
UserDefinedMessages
List all of the available folders by running the DIR command
If you want to list all the server level roles, run the following commands.
CD ROLES
DIR
Server level roles
If you want to see all of the logins starting with NT, we have to navigate one step, go to the login folder and use the wild cards like in Get-Childitem command as shown below:
CD..
CD Logins
DIR NT*
PS SQLSERVER:\SQL\USTESTSQL01\SQLENG\Roles> cd ..
PS SQLSERVER:\SQL\USTESTSQL01\SQLENG> cd Logins
PS SQLSERVER:\SQL\USTESTSQL01\SQLENG\Logins> dir NT*
Name Login Type Created
---- ---------- -------
NT SERVICE\Winmgmt WindowsUser 3/15/2012 11:13 AM
NT SERVICE\SQLWriter WindowsUser 3/15/2012 11:13 AM
NT SERVICE\SQLAgent$SQLENG WindowsUser 3/15/2012 11:13 AM
NT Service\MSSQL$SQLENG WindowsUser 3/15/2012 11:13 AM
NT AUTHORITY\SYSTEM WindowsUser 3/15/2012 11:13 AM
See all of the logins starting with NT
If you want to execute some TSQL you could use the Invoke-SQLCMD cmdlet as shown below.
Invoke-SQLCMD "select @@version;select db_name()"
Result:
WARNING: Using provider context. Server = USTESTSQL01\SQLENG.
Column1
-------
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) ...
Master
Invoke-SQLCMD "select @@version;select db_name()"
Invoke-SQLCMD "backup database master to disk ='c:\bak\master.bak'"
You could also use Backup-SQLDatabase cmdlet to back up a database .
Backup-SqlDatabase -ServerInstance USTESTSQL01\SQLENG -Database Master
If you want to learn and understand more about any cmdlets, make use of the Get-Help cmdlets as shown below.
Get-Help invoke-sqlcmd –full
Get-Help Backup-SqlDatabase –full
Get-Help cmdlets
Conclusion
As mentioned earlier, this article illustrated how to make use of the SQLPS module and how to access the SQL Server components and features via Windows PowerShell.
See all articles by MAK
» 
Comment and Contribute
