Categories
PowerShell

SQL Server PowerShell Module (SQLPS)

SQL Server provides a Windows PowerShell module called sqlps that is used to import the SQL Server components into Windows PowerShell. The sqlps module loads two Windows PowerShell modules:

  • A SQL Server provider, which enables a simple navigation mechanism similar to file system paths. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. You can then use familiar commands such as cd and dir to navigate the paths similar to the way you navigate folders in a command prompt window. You can use other commands, such as ren or del, to perform actions on the nodes in the path.
  • A set of cmdlets, which are commands used in Windows PowerShell scripts to specify a SQL Server action. The SQL Server cmdlets support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.

More details here: http://msdn.microsoft.com/en-us/library/hh245198.aspx

Although the SQLPS module is installed along with SQL Server, you do not have to install SQL Server to obtain the module. You simply need to install three stand-alone packages from the Microsoft® SQL Server® 2012 Feature Pack, available here:
http://www.microsoft.com/en-us/download/details.aspx?id=29065

Install the following packages in this order:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

Be sure to select the appropriate package platform for each, either x86 or x64.

To load the sqlps module in Windows PowerShell:

Import-Module sqlps

(You can include the -DisableNameChecking parameter if you’re concerned about suppressing the Encode-Sqlname and Decode-Sqlname warning.)

Thanks to Max Trinidad for his article explaining this information. http://www.maxtblog.com/2012/09/create-powershell-smo-scripts-without-installing-sql-server/

By Tommy Doan

BF-ITS(Systems)

One reply on “SQL Server PowerShell Module (SQLPS)”

Oldish, but very helpful. Install using

msiexec /i SQLSysClrTypes.msi

and so on to circumvent RDP restrictions. Works like a charm.

Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *