项目
12/14/2010

使用 SQL Server PowerShell 提供程序

SQL Server Windows PowerShell 提供程序用类似于文件系统路径的路径公开 SQL Server 对象的层次结构。可以使用路径来查找对象，然后使用 SQL Server 管理对象 (SMO) 模型中的方法来针对对象执行操作。

SQL Server 提供程序层次结构

可以用层次结构表示其数据或对象模型的产品使用 Windows PowerShell 提供程序来公开层次结构。该层次结构是使用与 Windows 文件系统所用结构相似的驱动器和路径结构公开的。

每个 Windows PowerShell 提供程序都实现一个或多个驱动器。每个驱动器都是相关对象的层次结构的根节点。SQL Server 提供程序实现一个 SQLSERVER: 驱动器。SQLSERVER: 驱动器有四个主文件夹。每个文件夹及其子文件夹表示一组可通过使用 SQL Server 管理对象模型访问的对象。当关注某个以这些主文件夹之一开始的路径中的子文件夹时，可以使用相关对象模型中的方法对该节点所表示的对象执行操作。下表列出了由 SQL Server 2008 提供程序实现的 Windows PowerShell 文件夹。

文件夹	SQL Server 对象模型命名空间	对象
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	数据库对象，如表、视图和存储过程。
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	基于策略的管理对象，如策略和方面。
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	已注册的服务器对象，如服务器组和已注册服务器。
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	数据收集器对象，如收集组和配置存储区。

例如，可以使用 SQLSERVER:\SQL 文件夹作为路径的开头，该路径可以表示 SMO 对象模型支持的任何对象。SQLSERVER:\SQL 路径的前导部分是 SQLSERVER:\SQL\计算机名称\实例名称。必须指定计算机名称。可为本地计算机指定 localhost 或 `(local`)。即使对于默认实例，也必须始终指定实例名称。对于默认实例，请指定 DEFAULT。实例名称后面的节点在对象类（如 Database 或 View）和对象名称（如 AdventureWorks）之间交替变化。架构不用对象类表示。在为架构中的顶层对象（如表或视图）指定节点时，必须以 SchemaName.ObjectName 格式指定对象名称。

这是 AdventureWorks 数据库的 Purchasing 架构中的 Vendor 表的路径，该数据库位于本地计算机上的数据库引擎默认实例中：

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

有关 SMO 对象模型层次结构的详细信息，请参阅 SMO 对象模型关系图。

路径中的对象类节点与相关对象模型中的集合类相关联。对象名节点与相关对象模型中的对象类相关联，如下表中所示。

路径	SMO 类
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases	DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks	Database

每次在路径中引用数据库引擎实例时，SQL Server 提供程序都会使用 SMO 打开与该实例的 Windows 身份验证连接。在建立连接时使用的是运行 Windows PowerShell 会话的 Windows 帐户的凭据。SQL Server 提供程序不使用 SQL Server 身份验证。

定位 SQL Server 路径

通过 Windows PowerShell 所实现的 cmdlet，可以在提供程序的层次结构中导航并针对当前对象执行基本操作。由于 cmdlet 会经常用到，因此它们具有简短的规范别名。还有一组将 cmdlet 映射到类似命令提示符命令的别名，以及另一组用于 UNIX shell 命令的别名。

SQL Server 提供程序实现了提供程序 cmdlets 的一部分，如下表中所示。

cmdlet	规范别名	cmd 别名	UNIX shell 别名	说明
Get-Location	gl	pwd	pwd	获取当前节点。
Set-Location	sl	cd、chdir	cd、chdir	更改当前节点。
Get-ChildItem	gci	dir	ls	列出存储在当前节点中的对象。
Get-Item	gi			返回当前项的属性。
Move-Item	mi	move	mv	移动对象。
Rename-Item	rni	rn	ren	重命名对象。
Remove-Item	ri	del、rd	rm、rmdir	删除对象。

例如，可以使用下面的其中一组 cmdlet 或别名，导航到 SQLSERVER:\SQL 文件夹并请求该文件夹的子项列表，从而检索可供使用的 SQL Server 实例的列表：

使用完整的 cmdlet 名称：

Set-Location SQLSERVER:\SQL
Get-ChildItem

使用规范别名：
```
sl SQLSERVER:\SQL
gci
```
使用 cmd 别名：
```
cd SQLSERVER:\SQL
dir
```

使用 UNIX shell 别名：

cd SQLSERVER:\SQL
ls

重要提示
某些 SQL Server 标识符（对象名称）包含 Windows PowerShell 不支持在路径名称中使用的字符。有关如何使用包含这些字符的名称的详细信息，请参阅在 PowerShell 中使用 SQL Server 标识符。

使用 Get-ChildItem

由 Get-ChildItem（或其 dir 和 ls 别名）返回的信息取决于您在 SQLSERVER: 路径中的位置。

路径位置	Get-ChildItem 结果
SQLSERVER:\SQL	返回本地计算机的名称。如果在连接到其他计算机上的数据库引擎实例时使用的是 SMO 或 WMI，则还将列出这些计算机。
SQLSERVER:\SQL\计算机名称	计算机上数据库引擎实例的列表。
SQLSERVER:\SQL\计算机名称\实例名称	实例中顶层对象类型（如 Endpoints、Certificates 和 Databases）的列表。
对象类节点，如 Databases	该类型的对象列表，如数据库列表：master、model、AdventureWorks。
对象名称节点，如 AdventureWorks	包含在该对象中的对象类型的列表。例如，数据库将列出表和视图之类的对象类型。

默认情况下，Get-ChildItem 不会列出任何系统对象。使用 Force 参数可查看系统对象，例如 sys 架构中的对象。

下面的示例列出本地计算机以及任何已经与其建立了 SMO 或 WMI 连接的计算机：

Set-Location SQLSERVER:\SQL
Get-ChildItem

下面的示例列出本地计算机上的数据库引擎实例：

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

下面的示例列出数据库引擎默认实例中可用的对象的主类。该列表中包括 Endpoints、Certificates 和 Databases 之类的名称：

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

下面的示例列出默认数据库引擎实例中可用的数据库。Force 参数用于包括系统数据库（如 master 和 model）：

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

针对路径节点执行操作

在导航到 Windows PowerShell 路径中的节点之后，可以执行两种类型的操作：

可以运行作用于节点的 Windows PowerShell cmdlet，如 Rename-Item。
可以调用相关 SQL Server 管理对象模型中的方法，如 SMO。例如，如果您导航到路径中的 Databases 节点，则可以使用 Database 类的方法和属性。

SQL Server 提供程序用于管理数据库引擎实例中的对象，而不能用于处理数据库中的数据。如果您已经导航到表或视图，则不能使用该提供程序选择、插入、更新或删除数据。可以使用 Invoke-Sqlcmd cmdlet 来查询或更改 Windows PowerShell 环境内表和视图中的数据。有关详细信息，请参阅使用 Invoke-Sqlcmd cmdlet。

列出方法和属性

可以使用 Get-Member cmdlet 来查看可供特定对象或对象类使用的方法和属性。

下面的示例演示如何查看 SMO Database 类的方法列表：

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

下面的示例将 Windows PowerShell 变量设置为 SMO Database 类并列出其属性：

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

还可以使用 Get-Member 列出与 Windows PowerShell 路径的结束节点相关联的方法和属性。

下面的示例导航到 SQLSERVER: 路径中的 Databases 节点，并列出集合属性：

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

下面的示例导航到 SQLSERVER: 路径中的 AdventureWorks 节点，并列出对象属性：

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

使用方法和属性

可以在 Windows PowerShell 命令中引用 SMO 属性。下面的示例使用 SMO Schema 属性，从 AdventureWorks 中的 Sales 架构中获取表的列表：

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

下面的示例使用 SMO Script 方法生成一个包含 CREATE VIEW 语句的脚本，您必须使用该脚本重新创建 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 }

下面的示例使用 SMO Create 方法创建一个数据库，然后使用 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

定义自定义驱动器

Windows PowerShell 允许用户定义虚拟驱动器，这些驱动器称为 Windows PowerShell 驱动器或 ps 驱动器。这些虚拟驱动器映射到路径语句的起始节点。使用它们的目的通常是为了缩短经常键入的路径。SQLSERVER: 路径可能会很长，会在 Windows PowerShell 窗口中占据一定的空间，而且需要键入大量内容。如果您打算针对特定的路径节点执行大量工作，则可以定义一个映射到该节点的自定义 Windows PowerShell 驱动器。例如，如果您打算在 AdventureWorks 数据库中执行大量工作，则可以创建一个 AWDB: 驱动器：

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

然后可以使用 AWDB: 驱动器将路径缩短为 AdventureWorks 对象，如 Purchasing.Vendor 表：

Set-Location AWDB:\Tables\Purchasing.Vendor

管理 SQL Server 身份验证连接

默认情况下，SQL Server 提供程序会使用所用的 Windows 帐户来建立与数据库引擎的 Windows 身份验证连接。若要建立 SQL Server 身份验证连接，必须将 SQL Server 登录凭据与虚拟驱动器关联起来，然后使用更改目录命令 (cd) 连接到该驱动器。在 Windows PowerShell 中，安全凭据只能与虚拟驱动器关联。

此脚本创建名为 sqldrive 的函数，您可使用该函数来创建与指定 SQL Server 身份验证登录名和实例相关联的虚拟驱动器。

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
}

然后，您就可以通过运行下面的命令创建名为 SQLAuth: 的虚拟驱动器：

sqldrive SQLAuth

sqldrive 函数提示您输入登录名的密码，并在您键入密码时屏蔽密码。以后您每次使用更改目录命令 (cd) 和 SQLAuth: 驱动器连接到一个路径时，所有操作都将使用您在创建驱动器时提供的 SQL Server 身份验证登录凭据来执行。

使用 Microsoft.SqlServer.Managment.Smo.Wmi 命名空间

除了与 \SQL、\SQLPolicy 和 \SQLRegistration 文件夹关联的 SQL Server 管理对象模型命名空间之外，还可以使用 Microsoft.SqlServer.Management.Smo.Wmi 命名空间中的类。此命名空间的最常见用途是查询和管理实现每个数据库引擎实例或策略存储区的服务的状态。

此示例显示如何使用 ManagedComputer 类来停止和启动运行数据库引擎的默认实例的服务。

# 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"

注意
若要对远程计算机使用此命名空间中的类，必须将 Windows 防火墙配置为允许 WMI DCOM 连接。有关详细信息，请参阅配置 Windows 防火墙以允许 SQL Server 访问。

管理 Tab 填写功能

Windows PowerShell Tab 填写功能减少了您的键入量。在键入路径或 cmdlet 名称的一部分之后，可以按 Tab 键获得其名称与已键入内容相匹配的项列表。之后，可以从该列表中选择所需的项，而不必键入该名称的其余部分。

如果正在处理的数据库中包含大量对象，则 Tab 填写列表可能会变得非常大。某些 SQL Server 对象类型（如视图）也具有大量系统对象。

SQL Server 管理单元引入了三个可用来控制由 Tab 填写功能和 Get-ChildItem 所提供的信息量的系统变量。

**$SqlServerMaximumTabCompletion =**n
指定要包括在 Tab 填写列表中的对象的最大数量。如果您在具有多于 n 个对象的路径节点处选择 Tab，则 Tab 填写列表会在 n 处被截断。n 为整数。默认值是 0，表示对所列出对象的数量没有限制。
**$SqlServerMaximumChildItems =**n
指定由 Get-ChildItem 显示的对象的最大数量。如果在具有多于 n 个对象的路径节点处运行 Get-ChildItem，则该列表会在 n 处被截断。n 为整数。默认值是 0，表示对所列出对象的数量没有限制。
$SqlServerIncludeSystemObjects = { $True | $False }
如果为 $True，则 Tab 填写功能和 Get-ChildItem 将显示系统对象。如果为 $False，则将不显示系统对象。默认设置为 $False。

下面的示例将对所有三个变量进行设置并列出其设置：

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

更改历史记录

更新的内容
修复了引用 Smo.Database 的代码块中的语法错误。删除了对 SQL: 驱动器的引用，该驱动器已替换为 SQLSERVER: 驱动器。

通过