SQL Server PowerShell
SQL Server 2012 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 2012 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 2012 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. |
|
Describes how to load only the SMO assemblies without the provider or cmdlets. |
|
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. |
|
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. |
|
Describes how to use the SQL Server provider to navigate a hierarchy of SQL Server objects. |
|
Describes how to use the SQL Server cmdlets that specify Database Engine actions such as running a Transact-SQL script. |
|
Describes how to specify SQL Server delimited identifiers that contain characters not supported by Windows 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. |
|
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. |
|
Describes how to use Get-Help to get information about the SQL Server components in the Windows PowerShell environment. |