SQL Server PowerShell

SQL Server 2016 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 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 Getting Started with Windows PowerShell.

SQL Server Versions

The SQL Server 2016 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 2016 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
Installing Microsoft® Windows PowerShell Extensions for Microsoft SQL Server 2016 . The PowerShell modules are installed by default when installing Microsoft SQL Server . 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 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