| title | SQL Server PowerShell | Microsoft Docs | |
|---|---|---|
| ms.custom | ||
| ms.date | 08/04/2016 | |
| ms.prod | sql-non-specified | |
| ms.prod_service | powershell | |
| ms.service | ||
| ms.component | powershell | |
| ms.reviewer | ||
| ms.suite | sql | |
| ms.technology |
|
|
| ms.tgt_pltfrm | ||
| ms.topic | article | |
| ms.assetid | 89b70725-bbe7-4ffe-a27d-2a40005a97e7 | |
| caps.latest.revision | 10 | |
| author | stevestein | |
| ms.author | sstein | |
| manager | craigg | |
| ms.workload | Active |
[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] [!INCLUDEssCurrent] supports Windows PowerShell.
Note
There are two SQL Server PowerShell modules; SqlServer and SQLPS. The SQLPS module is included with the SQL Server installation (for backwards compatability), but is no longer being updated. The most up-to-date PowerShell module is the SqlServer module. The SqlServer module contains updated versions of the cmdlets in SQLPS, and also includes new cmdlets to support the latest SQL features.
Previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS. To use PowerShell with SSMS 17.0 and later, the SqlServer module must be installed from the PowerShell Gallery.
To install the SqlServer module, see Install SQL Server PowerShell.
Why did the module change from SQLPS to SqlServer?
To ship SQL PowerShell updates, we had to change the identity of the SQL PowerShell module, as well as the wrapper known as SQLPS.exe. Because of this change, there are now two SQL PowerShell modules, the SQLPS module, and the SqlServer module.
Update your PowerShell scripts if they import the SQLPS module.
If you have any PowerShell scripts that run Import-Module -Name SQLPS, and you want to take advantage of the new provider functionality and new cmdlets, you must change them to Import-Module -Name SqlServer. The new module is installed to %Program Files\WindowsPowerShell\Modules\SqlServer. Therefore, you do not have to update the $env:PSModulePath variable. If you have scripts that use a third-party or community version of a module named SqlServer, use of the Prefix parameter to avoid name collisions. There is no change to the module used by SQL Server Agent.
The SqlServer module loads two Windows PowerShell snap-ins:
-
A [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 that support actions such as running a sqlcmd script containing [!INCLUDEtsql] or XQuery statements.
The [!INCLUDEssCurrent] PowerShell components can be used to manage instances of [!INCLUDEssVersion2000] or later. Instances of [!INCLUDEssVersion2005] must be running SP2 or later. Instances of [!INCLUDEssVersion2000] must be running SP4 or later. When the [!INCLUDEssCurrent] PowerShell components are used with earlier versions of [!INCLUDEssNoVersion], they are limited to the functionality available in those versions.
The Encode-Sqlname and Decode-Sqlname cmdlets help you specify SQL Server identifiers that contain characters not supported in PowerShell paths. For more information, see SQL Server Identifiers in PowerShell.
Use the Convert-UrnToPath cmdlet to convert a Unique Resource Name for a [!INCLUDEssDE] object to a path for the SQL Server PowerShell provider. For more information, see Convert URNs to SQL Server Provider Paths.
Query expressions are strings that use syntax similar to XPath to specify a set of criteria that enumerate one or more objects in an object model hierarchy. A Unique Resource Name (URN) is a specific type of query expression string that uniquely identifies a single object. For more information, see Query Expressions and Uniform Resource Names.
| Task Description | Topic |
|---|---|
| Installing Microsoft® Windows PowerShell Extensions for Microsoft [!INCLUDEssCurrent]. The PowerShell modules are installed by default when installing [!INCLUDEmsCoName] [!INCLUDEssNoVersion]. You can manually install the PowerShell Extensions for SQL Server 2016 by installing the following components from the Microsoft® SQL Server® 2016 Feature Pack: Microsoft® System CLR Types for Microsoft SQL Server® 2016 (SQLSysClrTypes.msi) Microsoft® SQL Server® 2016 Shared Management Objects (SharedManagementObjects.msi) Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016 (PowerShellTools.msi) |
Microsoft® SQL Server® 2016 Feature Pack. |
| Describes the preferred mechanism for running the [!INCLUDEssNoVersion] PowerShell components; to open a PowerShell session and load the sqlps module. The sqlps module loads in the [!INCLUDEssNoVersion] PowerShell provider and cmdlets, and the SQL Server Management Object (SMO) assemblies used by the provider and cmdlets. | Import the SQLPS Module](https://docs.microsoft.com/en-us/sql/powershell/import-the-sqlps-module) |
| Describes how to load only the SMO assemblies without the provider or cmdlets. | Load the SMO Assemblies in Windows PowerShell |
| Describes how to run a Windows PowerShell session by right-clicking a node in Object Explorer. [!INCLUDEssManStudio] launches a Windows PowerShell session, loads the sqlps module, and sets the SQL Server provider path to the object selected. | Run Windows PowerShell from SQL Server Management Studio |
| Describes how to create SQL Server Agent job steps that run a Windows PowerShell script. The jobs can then be scheduled to run at specific times or in response to events. | Run Windows PowerShell Steps in SQL Server Agent |
| Describes how to use the [!INCLUDEssNoVersion] provider to navigate a hierarchy of [!INCLUDEssNoVersion] objects. | SQL Server PowerShell Provider |
| Describes how to specify [!INCLUDEssNoVersion] delimited identifiers that contain characters not supported by Windows PowerShell. | SQL Server Identifiers in PowerShell |
| Describes how to make SQL Server Authentication connections. By default, the SQL Server PowerShell components use Windows Authentication connections using the Windows credentials of the process running Windows PowerShell. | Manage Authentication in Database Engine PowerShell |
| Describes how to use variables implemented by the SQL Server PowerShell provider to control how many objects are listed when using Windows PowerShell tab completion. This is particularly useful when working on databases that contain large numbers of objects. | Manage Tab Completion (SQL Server PowerShell) |