SQL Server PowerShell Provider

The SQL Server provider for Windows PowerShell exposes the hierarchy of SQL Server objects in paths similar to file system paths. You can use the paths to locate an object and then use methods from the SQL Server Management Object (SMO) models to perform actions on the objects.

Note

There are two SQL Server PowerShell modules; SqlServer and SQLPS.

The SqlServer module is the current PowerShell module to use.

The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

Install the SqlServer module from the PowerShell Gallery.

For more information, visit SQL Server PowerShell.

Benefits of the SQL Server PowerShell Provider

The paths implemented by the SQL Server provider enable easy and interactively reviewing of all objects in an instance of SQL Server. You can navigate the paths using Windows PowerShell aliases similar to the commands you typically use to navigate file system paths.

The SQL Server PowerShell hierarchy

Products whose data or object models can be represented in a hierarchy use Windows PowerShell providers to expose the hierarchies. The hierarchy is exposed using a drive and path structure similar to the Windows file system.

Each Windows PowerShell provider implements one or more drives. Each drive is the root node of a hierarchy of related objects. The SQL Server provider implements an SQLSERVER drive. The provider also defines a set of primary folders for the SQLSERVER drive. Each folder and its subfolders represent the objects that can be accessed using a SQL Server management object model. When you are focused on a subfolder in a path that starts with one of these primary folders, you can use the methods from the associated object model to perform actions on the object represented by the node. The Windows PowerShell folders implemented by the SQL Server provider are listed in the following table:

Folder	SQL Server object model namespace	Objects
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	Database objects, such as tables, views, and stored procedures.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	Policy-based management objects, such as policies and facets.
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers	Registered server objects, such as server groups and registered servers.
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Data collector objects, such as collection sets and configuration stores.
SQLSERVER:\SSIS	Microsoft.SqlServer.Management.IntegrationServices	SSIS objects such as projects, packages, and environments.
SQLSERVER:\XEvent	Microsoft.SqlServer.Management.XEvent	SQL Server Extended Events
SQLSERVER:\DatabaseXEvent	Microsoft.SqlServer.Management.XEventDbScoped	SQL Server Extended Events
SQLSERVER:\SQLAS	Microsoft.AnalysisServices	Analysis Services objects such as cubes, aggregations, and dimensions.

For example, you can use the SQLSERVER:\SQL folder to start paths representing any object the SMO object model supports. The leading part of a SQLSERVER:\SQL path is SQLSERVER:\SQL\ComputerName\InstanceName. The nodes after the instance name alternate between object collections (such as Databases or Views) and object names (such as AdventureWorks2022). Schemas are not represented as object classes. When you specify the node for a top-level object in a schema, such as a table or view, you must specify the object name in the format SchemaName.ObjectName.

The following example shows the path of the Vendor table in the Purchasing schema of the AdventureWorks2022 database in a default instance of the Database Engine on the local computer:

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2022\Tables\Purchasing.Vendor

For more information about the SMO object model hierarchy, see /sql/relational-databases/server-management-objects-smo/smo-object-model-diagram.

Collection nodes in a path are associated with a collection class in the associated object model. Object name nodes are associated with an object class in the associated object model, as in the following table:

Path	SMO class
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases	Microsoft.SqlServer.Management.Smo.DatabaseCollection>
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2022	Microsoft.SqlServer.Management.Smo.Database>

SQL Server Provider tasks

Task Description	Article
Describes how to use Windows PowerShell cmdlets to navigate through the nodes in a path and, for each node, get a list of the objects at that node.	Navigate SQL Server PowerShell Paths
Describes how to use the SMO methods and properties to report on and perform work on the object a node represents in a path. Also describes how to get a list of the SMO methods and properties for that node.	Work With SQL Server PowerShell Paths
Describes converting an SMO Uniform Resource Name (URN) to a SQL Server provider path.	Convert-UrnToPath
Describes how to open SQL Server Authentication connections using the SQL Server provider. By default, the provider uses Windows Authentication connections made using the Windows account credentials running the Windows PowerShell session.	Manage Authentication in Database Engine PowerShell

Next step

SQL Server PowerShell