Article
10/22/2012

Using the SQL Server PowerShell Provider

The SQL Server Windows PowerShell provider 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.

The SQL Server Provider 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 by using a drive and path structure similar to what the Windows file system uses.

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 a SQLSERVER: drive. The SQLSERVER: drive has four primary folders. Each folder and its subfolders represent the set of objects that can be accessed by 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 that is represented by the node. The Windows PowerShell folders implemented by the SQL Server 2008 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 Microsoft.SqlServer.Management.Smo.RegSvrEnum	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.

For example, you can use the SQLSERVER:\SQL folder to start paths that can represent any object that is supported by the SMO object model. The leading part of a SQLSERVER:\SQL path is SQLSERVER:\SQL\ComputerName\InstanceName. You must specify a computer name. You can specify either localhost or `(local`) for the local computer. You must always specify the instance name, even for default instances. For default instances, specify DEFAULT. The nodes after the instance name alternate between object classes (such as Database or View) and object names (such as AdventureWorks). 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.

This is the path of the Vendor table in the Purchasing schema of the AdventureWorks database in a default instance of the Database Engine on the local computer:

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

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

For more information about the SMO object model hierarchy, see SMO Object Model Diagram.

Object class 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	DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks	Database

Whenever you reference an instance of the Database Engine in a path, the SQL Server provider uses SMO to open a Windows Authentication connection to the instance. The connection is made using the credentials of the Windows account running the Windows PowerShell session. The SQL Server provider does not use SQL Server Authentication.

Navigating SQL Server Paths

Windows PowerShell implements cmdlets to navigate provider hierarchies and perform basic operations on the current object. Because the cmdlets are used frequently, they have short, canonical aliases. There is also one set of aliases that maps the cmdlets to similar command prompt commands, and another set for UNIX shell commands.

The SQL Server provider implements a subset of the provider cmdlets, shown in the following table.

cmdlet	Canonical alias	cmd alias	UNIX shell alias	Description
Get-Location	gl	pwd	pwd	Gets the current node.
Set-Location	sl	cd, chdir	cd, chdir	Changes the current node.
Get-ChildItem	gci	dir	ls	Lists the objects stored at the current node.
Get-Item	gi			Returns the properties of the current item.
Move-Item	mi	move	mv	Moves an object.
Rename-Item	rni	rn	ren	Renames an object.
Remove-Item	ri	del, rd	rm, rmdir	Removes an object.

For example, you can use one of the following sets of cmdlets or aliases to retrieve a listing of the SQL Server instances available to you by navigating to the SQLSERVER:\SQL folder and requesting the list of child items for the folder:

Using full cmdlet names:

Set-Location SQLSERVER:\SQL
Get-ChildItem

Set-Location SQLSERVER:\SQL
Get-ChildItem

Using canonical aliases:

sl SQLSERVER:\SQL
gci

sl SQLSERVER:\SQL
gci

Using cmd aliases:

cd SQLSERVER:\SQL
dir

cd SQLSERVER:\SQL
dir

Using UNIX shell aliases:
```
cd SQLSERVER:\SQL
ls

cd SQLSERVER:\SQL
ls
```
Important

Some SQL Server identifiers (object names) contain characters that Windows PowerShell does not support in path names. For more information about how to use names that contain these characters, see Using SQL Server Identifiers in PowerShell.

Using Get-ChildItem

The information returned by Get-ChildItem (or its dir and ls aliases) depends on your location in a SQLSERVER: path.

Path location	Get-ChildItem results
SQLSERVER:\SQL	Returns the name of the local computer. If you have used the SMO or WMI to connect to instances of the Database Engine on other computers, those computers are also listed.
SQLSERVER:\SQL\ComputerName	The list of instances of the Database Engine on the computer.
SQLSERVER:\SQL\ComputerName\InstanceName	The list of top-level object types in the instance, such as Endpoints, Certificates, and Databases.
Object class node, such as Databases	The list of objects of that type, such as the list of databases: master, model, AdventureWorks.
Object name node, such as AdventureWorks	The list of object types contained within the object. For example, a database would list object types such as tables and views.

By default, Get-ChildItem does not list any system objects. Use the Force parameter to see system objects, such as the objects in the sys schema.

This example lists the local computer and any computer to which you have made an SMO or WMI connection:

Set-Location SQLSERVER:\SQL
Get-ChildItem

Set-Location SQLSERVER:\SQL
Get-ChildItem

This example lists the instances of the Database Engine on the local computer:

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

This example lists the main classes of objects available in a default instance of the Database Engine. The list includes names like Endpoints, Certificates, and Databases:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

This example lists the databases available in a default instance of the Database Engine. The Force parameter is used to include the system databases like master and model:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

Performing Actions on Path Nodes

After you have navigated to a node in a Windows PowerShell path, you can perform two types of actions:

You can run Windows PowerShell cmdlets that operate on nodes, such as Rename-Item.
You can call the methods from the associated SQL Server management object model, such as SMO. For example, if you navigate to the Databases node in a path, you can use the methods and properties of the Database class.

The SQL Server provider is used to manage the objects in an instance of the Database Engine. It is not used to work with the data in databases. If you have navigated to a table or view, you cannot use the provider to select, insert, update, or delete data. Use the Invoke-Sqlcmd cmdlet to query or change data in tables and views from the Windows PowerShell environment. For more information, see Using the Invoke-Sqlcmd cmdlet.

Listing Methods and Properties

You can use the Get-Member cmdlet to view the methods and properties available for specific objects or object classes.

This example shows how to see a list of the methods of the SMO Database class:

[Microsoft.SqlServer.Management.SMO.Database] | Get-Member -Type Methods

[Microsoft.SqlServer.Management.SMO.Database] | Get-Member -Type Methods

This example sets a Windows PowerShell variable to the SMO Database class and lists the properties:

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member -Type Properties

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member -Type Properties

You can also use Get-Member to list the methods and properties that are associated with the end node of a Windows PowerShell path.

This example navigates to the Databases node in a SQLSERVER: path and lists the collection properties:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

This example navigates to the AdventureWorks node in a SQLSERVER: path and lists the object properties:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
Get-Item . | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
Get-Item . | Get-Member -Type Properties

Using Methods and Properties

You can reference SMO properties in Windows PowerShell commands. This example uses the SMO Schema property to get a list of the tables from the Sales schema in AdventureWorks:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

This example uses the SMO Script method to generate a script that contains the CREATE VIEW statements you must have to re-create the views in AdventureWorks:

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

This example uses the SMO Create method to create a database, and then uses the State property to show whether the database exists:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State

Defining Custom Drives

Windows PowerShell lets users define virtual drives, which are referred to as Windows PowerShell drives or ps drives. These map over the starting nodes of a path statement. They are typically used to shorten paths that are typed frequently. SQLSERVER: paths can get long, taking space in the Windows PowerShell window and requiring a lot of typing. If you are going to do a lot of work at a particular path node, you can define a custom Windows PowerShell drive that maps to that node. For example, if you are doing a lot of work in the AdventureWorks database, you can create an AWDB: drive:

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks

You can then use the AWDB: drive to shorten the path to AdventureWorks objects, such as the Purchasing.Vendor table:

Set-Location AWDB:\Tables\Purchasing.Vendor

Set-Location AWDB:\Tables\Purchasing.Vendor

Managing SQL Server Authentication Connections

By default, the SQL Server provider uses the Windows account under which it is running to make a Windows Authentication connection to the Database Engine. To make a SQL Server Authentication connection, you must associate the SQL Server login credentials with a virtual drive, and then use the change directory command (cd) to connect to that drive. In Windows PowerShell, security credentials can only be associated with virtual drives.

This script creates a function named sqldrive that you can use to create a virtual drive that is associated with the specified SQL Server Authentication login and instance.

function sqldrive
{
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
    $pwd = read-host -AsSecureString -Prompt "Password"
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}

function sqldrive
{
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
    $pwd = read-host -AsSecureString -Prompt "Password"
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}

You can then create a virtual drive named SQLAuth: by running this command:

sqldrive SQLAuth

sqldrive SQLAuth

The sqldrive function prompts you to enter the password for your login, masking the password as you type it in. Then, whenever you use the change directory command (cd) to connect to a path by using the SQLAuth: drive, all operations are performed by using the SQL Server Authentication login credentials that you supplied when you created the drive.

Using the Microsoft.SqlServer.Managment.Smo.Wmi Namespace

In addition to the SQL Server Management Object Model namespaces that are associated with the \SQL, \SQLPolicy, and \SQLRegistration folders, you can also use classes in the Microsoft.SqlServer.Management.Smo.Wmi namespace. The most common use of this namespace is to query and manage the state of the services that implement each instance of the Database Engine or a policy store.

This example shows how to use the ManagedComputer class to stop and start the service running a default instance of the Database Engine.

# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"

# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"

Note

To use the classes in this namespace against remote computers, you must configure your Windows Firewall to allow WMI DCOM connections. For more information, see Configuring the Windows Firewall to Allow SQL Server Access.

Managing Tab-Completion

Windows PowerShell tab-completion reduces the amount of typing you must do. When you have typed part of a path or cmdlet name, you can hit the Tab key to get a list of the items whose names match what you have already typed. You can then select the item you want from the list without having to type the rest of the name.

If you are working in a database that has a lot of objects, the tab-completion lists can become very large. Some SQL Server object types, such as views, also have large numbers of system objects.

The SQL Server snap-ins introduce three system variables that you can use to control the amount of information presented by tab-completion and Get-ChildItem.

**$SqlServerMaximumTabCompletion =**n
Specifies the maximum number of objects to include in a tab-completion list. If you select Tab at a path node having more than n objects, the tab-completion list is truncated at n. n is an integer. 0 is the default setting, and means there is no limit to the number of objects listed.
**$SqlServerMaximumChildItems =**n
Specifies the maximum number of objects displayed by Get-ChildItem. If Get-ChildItem is run at a path node having more than n objects, the list is truncated at n. n is an integer. 0 is the default setting, and means there is no limit to the number of objects listed.
$SqlServerIncludeSystemObjects = { $True | $False }
If $True, system objects are displayed by tab-completion and Get-ChildItem. If $False, no system objects are displayed. The default setting is $False.

The following example sets all three variables and lists their settings:

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*

Change History

Updated content
Fixed syntax errors in code blocks referencing Smo.Database. Removed references to the SQL: drive that was replaced with the SQLSERVER: drive.

Share via