發行項
12/15/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	以原則為基礎的管理物件，例如原則和 Facet。
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	已註冊的伺服器物件，例如伺服器群組和已註冊的伺服器。
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	資料收集器物件，例如收集組和組態存放區。

例如，您可以使用 SQLSERVER:\SQL 資料夾來當做可代表 SMO 物件模型所支援之任何物件的路徑開頭。SQLSERVER:\SQL 路徑的前置部分是 SQLSERVER:\SQL\ComputerName\InstanceName。您必須指定電腦名稱。您可以為本機電腦指定 localhost 或 `(local`)。您一定要指定執行個體名稱，即使是預設執行個體也一樣。如果是預設執行個體，請指定 DEFAULT。執行個體名稱之後的節點會在物件類別 (如 Database 或 View) 和物件名稱 (如 AdventureWorks) 之間輪替。結構描述不會表示為物件類別。當您在結構描述中指定最上層物件的節點 (如資料表或檢視表) 時，必須使用 SchemaName.ObjectName 格式來指定物件名稱。

這是本機電腦上預設 Database Engine 執行個體中 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

每當您參考路徑中的 Database Engine 執行個體時，SQL Server 提供者都會使用 SMO 開啟此執行個體的 Windows 驗證連接。此連接是使用執行 Windows PowerShell 工作階段的 Windows 帳戶認證來進行。SQL Server 提供者不會使用 SQL Server 驗證。

導覽 SQL Server 路徑

Windows PowerShell 會實作指令程式來導覽提供者階層，並針對目前的物件執行基本作業。由於指令程式會經常被使用，所以具有簡短、標準的別名。也有一組別名會將指令程式對應到類似的命令提示字元命令，而且有另一組別名適用於 UNIX Shell 命令。

SQL Server 提供者會實作提供者指令程式的子集，如下表所示。

指令程式	標準的別名	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	移除物件。

例如，您可以使用下列其中一組指令程式或別名來擷取可用的 SQL Server 執行個體清單，方式是導覽至 SQLSERVER:\SQL 資料夾，並要求該資料夾的子項目清單：

使用完整的指令程式名稱：

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 連接到其他電腦上的 Database Engine 執行個體，也會列出這些電腦。
SQLSERVER:\SQL\ComputerName	電腦上 Database Engine 執行個體的清單。
SQLSERVER:\SQL\ComputerName\InstanceName	執行個體中最上層物件類型的清單，例如 Endpoints、Certificates 和 Databases。
物件類別節點，例如 Databases	該類型的物件清單，例如資料庫的清單：master、model、AdventureWorks。
物件名稱節點，例如 AdventureWorks。	此物件內所包含的物件類型清單。例如，資料庫會列出類似資料表和檢視表的物件類型。

根據預設，Get-ChildItem 不會列出任何系統物件。使用 Force 參數來查看系統物件，例如 sys 結構描述中的物件。

此範例會列出本機電腦以及您已經進行 SMO 或 WMI 連接的任何電腦：

Set-Location SQLSERVER:\SQL
Get-ChildItem

此範例會列出本機電腦上的 Database Engine 執行個體：

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

此範例會列出預設 Database Engine 執行個體中可用之物件的主要類別。此清單包括類似 Endpoints、Certificates 和 Databases 的名稱：

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

此範例會列出預設 Database Engine 執行個體中可用的資料庫。Force 參數可用來加入系統資料庫，例如 master 和 model：

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

在路徑節點上執行動作

當您導覽至 Windows PowerShell 路徑中的節點之後，您可以執行兩種動作：

您可以執行在節點上運作的 Windows PowerShell 指令程式，例如 Rename-Item。
您可以從關聯的 SQL Server 管理物件模型 (如 SMO) 呼叫方法。例如，如果您導覽至路徑中的 Databases 節點，您可以使用 Database 類別的方法和屬性。

SQL Server 提供者是用來管理 Database Engine 執行個體中的物件，而不是用來處理資料庫中的資料。如果您導覽至資料表或檢視表，您就無法使用此提供者來選取、插入、更新或刪除資料。使用 Invoke-Sqlcmd 指令程式可從 Windows PowerShell 環境來查詢或變更資料表和檢視表中的資料。如需詳細資訊，請參閱＜使用 Invoke-Sqlcmd 指令程式＞。

列出方法和屬性

您可以使用 Get-Member 指令程式來檢視特定物件或物件類別的可用方法和屬性。

此範例會示範如何查看 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 結構描述屬性來取得 AdventureWorks 中 Sales 結構描述內的資料表清單：

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

此範例會使用 SMO 指令碼方法來產生包含 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 提供者會使用用來建立 Database Engine 之 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 命名空間中的類別。這個命名空間最常用來查詢和管理實作每個 Database Engine 執行個體或原則存放區之服務的狀態。

這則範例將示範如何使用 ManagedComputer 類別來停止和啟動執行預設 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"

[!附註]

若要針對遠端電腦使用這個命名空間中的類別，您必須將 Windows 防火牆設定成允許 WMI DCOM 連接。如需詳細資訊，請參閱＜將 Windows 防火牆設定成允許 SQL Server 存取＞。

管理 Tab-Completion

Windows PowerShell Tab-Completion 會減少您必須做的輸入量。當您已輸入一部分的路徑或指令程式名稱時，您可以按 Tab 鍵來取得名稱符合您已輸入之項目的項目清單。然後您可以從清單中選取想要的項目，而不必輸入名稱的其餘部分。

如果您正在擁有許多物件的資料庫中工作，索引標籤完成的清單會變得很大。某些 SQL Server 物件類型 (如檢視表) 也會有大量的系統物件。

SQL Server 嵌入式管理單元導入三個系統變數，您可以使用這些變數來控制 Tab-Completion 和 Get-ChildItem 所呈現的資訊量。

**$SqlServerMaximumTabCompletion =**n
指定 tab-completion 清單中要包含的最大物件數目。如果您在有 n 個物件以上的路徑節點上選取 Tab 鍵，Tab-Completion 清單就會在 n 處截斷。n 為整數。預設值為 0，表示列出的物件數沒有限制。
**$SqlServerMaximumChildItems =**n
指定 Get-ChildItem 顯示的最大物件數。如果 Get-ChildItem 在具有 n 個物件以上的路徑節點上執行，此清單會在 n 處截斷。n 為整數。預設值為 0，表示列出的物件數沒有限制。
$SqlServerIncludeSystemObjects = { $True | $False }
如果為 $True，表示系統物件是由 Tab-Completion 和 Get-ChildItem 所顯示。如果為 $False，表示未顯示任何系統物件。預設設定是 $False。

以下範例會設定所有的三個變數，並列出其設定：

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

變更記錄

更新的內容
已修正參考 Smo.Database 之程式碼區塊中的語法錯誤。已移除以 SQLSERVER: 磁碟機取代之 SQL: 磁碟機的參考。

共用方式為