| title | SQL Server PowerShell | Microsoft Docs | |
|---|---|---|
| ms.custom | ||
| ms.date | 08/04/2016 | |
| ms.prod | sql-non-specified | |
| ms.prod_service | sql-tools | |
| ms.service | ||
| ms.component | ssms-scripting | |
| 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 | JennieHubbard | |
| ms.author | jhubbard | |
| manager | jhubbard | |
| ms.workload | Active |
[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] [!INCLUDEssCurrent] supports Windows PowerShell, which is a powerful scripting shell that lets administrators and developers automate server administration and application deployment. The Windows PowerShell language supports more complex logic than [!INCLUDEtsql] scripts, giving [!INCLUDEssNoVersion] administrators the ability to build robust administration scripts. Windows PowerShell scripts can also be used to administer other [!INCLUDEmsCoName] server products. This gives administrators a common scripting language across servers.
[!INCLUDEssNoVersion] provides a Windows PowerShell module named sqlps that is used to import the [!INCLUDEssNoVersion] components into a Windows PowerShell environment or script. The sqlps module loads two Windows PowerShell snap-ins that implement:
-
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, which are commands used in Windows PowerShell scripts to specify a [!INCLUDEssNoVersion] action. The [!INCLUDEssNoVersion] cmdlets support actions such as running a sqlcmd script containing [!INCLUDEtsql] or XQuery statements.
To learn about Windows PowerShell, see Getting Started with Windows PowerShell.
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.
| 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 |
| 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 use the [!INCLUDEssNoVersion] cmdlets that specify [!INCLUDEssDE] actions such as running a [!INCLUDEtsql] script. | Use the Database Engine cmdlets |
| 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) |
| Describes how to use Get-Help to get information about the [!INCLUDEssNoVersion] components in the Windows PowerShell environment. | Get Help SQL Server PowerShell |