SQL Server PowerShell
SQL Server 2014 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 Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts. Windows PowerShell scripts can also be used to administer other Microsoft server products. This gives administrators a common scripting language across servers.
SQL Server PowerShell Components
SQL Server provides a Windows PowerShell module named sqlps that is used to import the SQL Server components into a Windows PowerShell 2.0 environment or script. The sqlps module loads two Windows PowerShell snap-ins that implement:
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.
To learn about Windows PowerShell, see the Windows PowerShell Getting Started Guide.
SQL Server Versions
The SQL Server 2014 PowerShell components can be used to manage instances of SQL Server 2000 or later. Instances of SQL Server 2005 must be running SP2 or later. Instances of SQL Server 2000 must be running SP4 or later. When the SQL Server 2014 PowerShell components are used with earlier versions of SQL Server, they are limited to the functionality available in those versions.
SQL Server PowerShell Tasks
| Task Description | Topic |
|---|---|
Describes the preferred mechanism for running the SQL Server PowerShell components; to open a PowerShell session and load the sqlps module. The sqlps module loads in the SQL Server 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. Management Studio 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 SQL Server provider to navigate a hierarchy of SQL Server objects. | SQL Server PowerShell Provider |
| Describes how to use the SQL Server cmdlets that specify Database Engine actions such as running a Transact-SQL script. | Use the Database Engine cmdlets |
| Describes how to specify SQL Server 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 SQL Server components in the Windows PowerShell environment. | Get Help SQL Server PowerShell |