Artikel
03/30/2012

Verwenden des SQL Server PowerShell-Anbieters

Der SQL Server-Anbieter für Windows PowerShell macht die Hierarchie von SQL Server-Objekten auf ähnliche Weise wie in Dateisystempfaden verfügbar. Mithilfe der Pfade können Sie Objekte finden und dann Methoden der SQL Server Management Object (SMO)-Modelle verwenden, um Aktionen an den Objekten auszuführen.

Die SQL Server PowerShell-Hierarchie

Produkte, deren Daten oder Objektmodelle in einer Hierarchie dargestellt werden können, verwenden Windows PowerShell-Anbieter, um die Hierarchien verfügbar zu machen. Die Hierarchie wird mithilfe einer Laufwerks- und Pfadstruktur verfügbar gemacht, die der für das Windows-Dateisystem verwendeten Struktur ähnelt.

Jeder Windows PowerShell-Anbieter implementiert ein oder mehrere Laufwerke. Jedes Laufwerk ist der Stammknoten einer Hierarchie verwandter Objekte. Der SQL Server-Anbieter implementiert ein Laufwerk mit der Bezeichnung SQLSERVER:. Das Laufwerk SQLSERVER: weist vier primäre Ordner auf. Jeder Ordner und seine Unterordner stellen den Satz von Objekten dar, auf die über ein SQL Server -Verwaltungsobjektmodell zugegriffen werden kann. Wenn sich der Fokus auf einem Unterordner in einem Pfad befindet, der mit einem dieser primären Ordner beginnt, können Sie die Methoden des zugeordneten Objektmodells verwenden, um Aktionen für das vom Knoten dargestellte Objekt auszuführen. Die vom SQL Server 2008 R2-Anbieter implementierten Windows PowerShell-Ordner werden in der folgenden Tabelle aufgelistet.

Ordner	Namespace des SQL Server-Objektmodells	Objekte
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	Datenbankobjekte, wie Tabellen, Sichten und gespeicherte Prozeduren.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	Richtlinienbasierte Verwaltungsobjekte, z. B. Richtlinien und Facets
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	Registrierte Serverobjekte, z. B. Servergruppen und registrierte Server
SQLSERVER:\Utility	Microsoft.SqlServer.Management.Utility	Hilfsprogrammobjekte, z. B. verwaltete Database Engine (Datenbankmodul)-Instanzen
SQLSERVER:\DAC	Microsoft.SqlServer.Management.DAC	Datenebenenanwendungs-Objekte z. B. DAC-Pakete und Vorgänge wie das Bereitstellen einer DAC
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Datensammler-Objekte, wie Sammlungssätze und Konfigurationsspeicher

Sie können z. B. mit dem Ordner SQLSERVER:\SQL Pfade beginnen, die jedes vom SMO-Objektmodell unterstützte Objekt darstellen können. Der erste Teil eines SQLSERVER:\SQL-Pfads ist SQLSERVER:\SQL\ComputerName\InstanceName. Sie müssen einen Computernamen angeben. Sie können entweder localhost oder (local) für den lokalen Computer angeben. Sie müssen immer den Instanznamen angeben, auch für Standardinstanzen. Geben Sie für Standardinstanzen DEFAULT an. Die Knoten nach dem Instanznamen wechseln zwischen Objektklassen (z. B. Database oder View) und Objektnamen (z. B. AdventureWorks2008R2). Schemas werden nicht als Objektklassen dargestellt. Wenn Sie den Knoten für ein Objekt der höchsten Ebene in einem Schema angeben, wie beispielsweise eine Tabelle oder eine Sicht, müssen Sie den Objektnamen im Format Schemaname.Objektname angeben.

Dies ist der Pfad der Vendor-Tabelle im Purchasing-Schema der AdventureWorks2008R2-Datenbank in einer Standardinstanz von Database Engine (Datenbankmodul) auf dem lokalen Computer:

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

Weitere Informationen zur SMO-Objektmodellhierarchie finden Sie unter Diagramm des SMO-Objektmodells.

Objektklassenknoten in einem Pfad werden im zugeordneten Objektmodell einer Auflistungsklasse zugeordnet. Objektnamenknoten werden wie in der folgenden Tabelle einer Objektklasse im zugeordneten Objektmodell zugeordnet.

Pfad	SMO-Klasse
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases	DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2	Database

Immer, wenn Sie in einem Pfad auf eine Instanz von Database Engine (Datenbankmodul) verweisen, verwendet der SQL Server-Anbieter SMO, um eine Windows-Authentifizierungsverbindung mit der Instanz zu öffnen. Die Verbindung wird mithilfe der Anmeldedaten des Windows-Kontos hergestellt, das die Windows PowerShell-Sitzung ausführt. Der SQL Server-Anbieter verwendet keine SQL Server-Authentifizierung.

Navigieren in SQL Server-Pfaden

Windows PowerShell implementiert Cmdlets, um in Anbieterhierarchien zu navigieren und grundlegende Vorgänge an dem aktuellen Objekt auszuführen. Da die Cmdlets häufig verwendet werden, haben sie kurze, kanonische Aliase. Es gibt auch einen Satz von Aliasen, der die Cmdlets ähnlichen Eingabeaufforderungsbefehlen zuordnet, und einen weiteren Satz für UNIX-Shell-Befehle.

Der SQL Server-Anbieter implementiert eine Teilmenge der Anbieter-Cmdlets, wie in der folgenden Tabelle aufgeführt.

Cmdlet	Kanonischer Alias	Cmd-Alias	UNIX-Shell-Alias	Beschreibung
Get-Location	gl	pwd	pwd	Ruft den aktuellen Knoten ab.
Set-Location	sl	cd, chdir	cd, chdir	Ändert den aktuellen Knoten.
Get-ChildItem	gci	dir	ls	Listet die am aktuellen Knoten gespeicherten Objekte auf.
Get-Item	gi			Gibt die Eigenschaften des aktuellen Elements zurück.
Rename-Item	rni	rn	ren	Benennt ein Objekt um.
Remove-Item	ri	del, rd	rm, rmdir	Entfernt ein Objekt.

Sie können beispielsweise einen der folgenden Sätze von Cmdlets oder Aliasen verwenden, um eine Auflistung der Ihnen zur Verfügung stehenden SQL Server-Instanzen abzurufen, indem Sie zum SQLSERVER:\SQL-Ordner navigieren und die Liste der untergeordneten Elemente des Ordners anfordern:

Mit vollständigen Cmdlet-Namen:

Set-Location SQLSERVER:\SQL
Get-ChildItem

Mit kanonischen Aliasen:
```
sl SQLSERVER:\SQL
gci
```
Mit cmd-Aliasen:
```
cd SQLSERVER:\SQL
dir
```

Mit UNIX-Shell-Aliasen:

cd SQLSERVER:\SQL
ls

Wichtig
Einige SQL Server-Bezeichner (Objektnamen) enthalten Zeichen, die Windows PowerShell in Pfadnamen nicht unterstützt. Weitere Informationen zum Verwenden von Namen, die diese Zeichen enthalten, finden Sie unter Verwenden von SQL Server-Bezeichnern in PowerShell.

Verwenden von Get-ChildItem

Die von Get-ChildItem (oder den zugehörigen Aliasen dir und ls) zurückgegebenen Informationen sind von Ihrer Position in einem SQLSERVER:-Pfad abhängig.

Pfadposition	Get-ChildItem-Ergebnisse
SQLSERVER:\SQL	Gibt den Namen des lokalen Computers zurück. Wenn Sie die Verbindung mit Instanzen von Database Engine (Datenbankmodul) auf anderen Computern mithilfe von SMO oder WMI hergestellt haben, werden diese Computer ebenfalls aufgelistet.
SQLSERVER:\SQL\ComputerName	Die Liste der Instanzen von Database Engine (Datenbankmodul) auf dem Computer.
SQLSERVER:\SQL\ComputerName\InstanceName	Die Liste von Objekttypen der höchsten Ebene in der Instanz, wie Endpunkte, Zertifikate und Datenbanken.
Objektklassenknoten, z. B. Datenbanken	Die Liste der Objekte dieses Typs, z. B. die Liste von Datenbanken: master, model, AdventureWorks20008R2.
Objektnamenknoten, z. B. AdventureWorks2008R2	Die Liste von im Objekt enthaltenen Objekttypen. Zum Beispiel würden in einer Datenbank Objekttypen wie Tabellen und Sichten aufgeführt werden.

Standardmäßig listet Get-ChildItem keine Systemobjekte auf. Verwenden Sie den Parameter Force, um Systemobjekte anzuzeigen, wie z. B. die Objekte im sys-Schema.

In diesem Beispiel werden der lokale Computer und alle Computer, mit denen eine SMO- oder WMI-Verbindung besteht, aufgelistet:

Set-Location SQLSERVER:\SQL
Get-ChildItem

In diesem Beispiel werden die Instanzen von Database Engine (Datenbankmodul) auf dem lokalen Computer aufgeführt:

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

In diesem Beispiel werden die Hauptklassen von in einer Standardinstanz von Database Engine (Datenbankmodul) verfügbaren Objekten aufgeführt. Die Liste umfasst Namen wie Endpunkte, Zertifikate und Datenbanken:

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

In diesem Beispiel werden die in einer Standardinstanz des Datenbankmoduls verfügbaren Datenbanken aufgeführt. Der Force-Parameter wird verwendet, um die Systemdatenbanken, z. B. master und model, einzuschließen:

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

Ausführen von Aktionen für Pfadknoten

Nachdem Sie zu einem Knoten in einem Windows PowerShell-Pfad navigiert sind, können Sie zwei Arten von Aktionen ausführen:

Sie können Windows PowerShell-Cmdlets ausführen, die Vorgänge an Knoten durchführen, z.B. Rename-Item.
Sie können die Methoden vom zugeordneten SQL Server Management Object-Modell aufrufen, z. B. SMO. Wenn Sie beispielsweise zum Knoten Databases in einem Pfad navigieren, können Sie die Methoden und Eigenschaften der Database-Klasse verwenden.

Der SQL Server-Anbieter wird zum Verwalten der Objekte in einer Instanz von Database Engine (Datenbankmodul) verwendet. Er wird nicht verwendet, um mit den Daten in Datenbanken zu arbeiten. Wenn Sie zu einer Tabelle oder einer Sicht navigiert sind, können Sie den Anbieter nicht dazu verwenden, Daten auszuwählen, einzufügen, zu aktualisieren oder zu löschen. Verwenden Sie das Cmdlet Invoke-Sqlcmd, um Daten in Tabellen und Sichten aus der Windows PowerShell-Umgebung abzufragen oder zu ändern. Weitere Informationen finden Sie unter Verwenden des Invoke-Sqlcmd-Cmdlets.

Auflisten von Methoden und Eigenschaften

Sie können das Cmdlet Get-Member verwenden, um die für bestimmte Objekte oder Objektklassen verfügbaren Methoden und Eigenschaften anzuzeigen.

In diesem Beispiel wird eine Windows PowerShell-Variable auf die Database-Klasse von SMO festgelegt. Zudem werden die Methoden und Eigenschaften aufgeführt:

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

Sie können auch Get-Member verwenden, um die dem Endknoten eines Windows PowerShell-Pfads zugeordneten Methoden und Eigenschaften aufzulisten.

In diesem Beispiel wird zum Knoten Databases in einem SQLSERVER:-Pfad navigiert, und die Auflistungseigenschaften werden aufgeführt:

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

In diesem Beispiel wird zum Knoten AdventureWorks2008R2 in einem SQLSERVER:-Pfad navigiert, und die Objekteigenschaften werden aufgeführt:

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

Verwenden von Methoden und Eigenschaften

Sie können auf SMO-Eigenschaften in Windows PowerShell-Befehlen verweisen. In diesem Beispiel wird die SMO-Schema-Eigenschaft verwendet, um eine Liste der Tabellen aus dem Sales-Schema in AdventureWorks2008R2 abzurufen:

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

In diesem Beispiel wird die SMO-Script-Methode verwendet, um ein Skript zu erstellen, das die CREATE VIEW-Anweisungen enthält, die Sie zur erneuten Erstellung der Sichten in AdventureWorks2008R2 benötigen:

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

In diesem Beispiel wird die Create-Methode von SMO verwendet, um eine Datenbank zu erstellen, und anschließend die State-Eigenschaft, um anzuzeigen, ob die Datenbank vorhanden ist:

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

Definieren von benutzerdefinierten Laufwerken

Mit Windows PowerShell können Benutzer virtuelle Laufwerke definieren, die als PowerShell-Laufwerke bezeichnet werden. Diese werden über die Startknoten einer Pfadanweisung zugeordnet. Sie werden in der Regel verwendet, um Pfade, die häufig eingegeben werden, zu kürzen. SQLSERVER: Pfade können lang werden, somit viel Platz im Windows PowerShell-Fenster einnehmen und umfangreiche Eingaben erfordern. Wenn Sie vorhaben, viel mit einem bestimmten Pfadknoten zu arbeiten, können Sie ein benutzerdefiniertes Windows PowerShell-Laufwerk definieren, das dem Knoten zugeordnet ist. Wenn Sie z. B. viel in der AdventureWorks2008R2-Datenbank arbeiten, können Sie ein Laufwerk mit dem Namen AWDB: erstellen:

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

Sie können dann das Laufwerk AWDB: verwenden, um den Pfad zu AdventureWorks2008R2-Objekten, z. B. zur Tabelle Purchasing.Vendor, zu verkürzen:

Set-Location AWDB:\Tables\Purchasing.Vendor

Verwalten von SQL Server-Authentifizierungsverbindungen

Der SQL Server-Anbieter verwendet standardmäßig das Windows-Konto, unter dem er ausgeführt wird, um eine Windows-Authentifizierungsverbindung mit der Database Engine (Datenbankmodul) herzustellen. Um eine Verbindung mit der SQL Server-Authentifizierung herzustellen, müssen Sie die SQL Server-Anmeldeinformationen einem virtuellen Laufwerk zuordnen. Anschließend können Sie mithilfe des Befehls zum Ändern des Verzeichnisses (cd) eine Verbindung mit diesem Laufwerk herstellen. In Windows PowerShell können Sicherheitsanmeldeinformationen nur virtuellen Laufwerken zugeordnet werden.

Dieses Skript erstellt eine Funktion mit dem Namen sqldrive, mit der Sie ein virtuelles Laufwerk erstellen können, das mit dem angegebenen Anmeldenamen für die SQL Server-Authentifizierung und der angegebenen Instanz verknüpft ist.

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
}

Sie können dann ein virtuelles Laufwerk mit dem Namen SQLAuth: erstellen, indem Sie folgenden Befehl ausführen:

sqldrive SQLAuth

Die Funktion sqldrive fordert Sie auf, das Kennwort für Ihren Anmeldenamen einzugeben, das bei der Eingabe maskiert wird. Wenn Sie anschließend den Befehl zum Ändern eines Verzeichnisses (cd) verwenden, um über das SQLAuth:-Laufwerk eine Verbindung mit einem Pfad herzustellen, werden stets alle Vorgänge mithilfe der Anmeldeinformationen für die SQL Server-Authentifizierung ausgeführt, die Sie beim Erstellen des Laufwerks angegeben haben.

Verwenden des Microsoft.SqlServer.Managment.Smo.Wmi-Namespaces

Zusätzlich zu den Namespaces des SQL Server Management Object-Modells, die mit den Ordnern \SQL, \SQLPolicy und \SQLRegistration verknüpft sind, können Sie auch Klassen im Namespace Microsoft.SqlServer.Management.Smo.Wmi verwenden. Die häufigste Verwendung dieses Namespaces besteht darin, den Status der Dienste, die jede Instanz von Database Engine (Datenbankmodul) oder einen Richtlinienspeicher implementieren, abzufragen und zu verwalten.

Dieses Beispiel zeigt, wie der Dienst, der eine Database Engine (Datenbankmodul)-Standardinstanz ausführt, mithilfe der ManagedComputer-Klasse gestartet und beendet werden kann.

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

Hinweis
Um die Klassen in diesem Namespace auf Remotecomputern zu verwenden, müssen Sie Ihre Windows-Firewall so konfigurieren, dass WMI DCOM-Verbindungen zugelassen werden. Weitere Informationen finden Sie unter Konfigurieren der Windows-Firewall für den SQL Server-Zugriff.

Verwalten der Befehlszeilenergänzung

Die Windows PowerShell-Befehlszeilenergänzung reduziert den Tippaufwand. Sobald Sie einen Teil eines Pfad- oder Cmdlet-Namens eingegeben haben, können Sie die TAB-TASTE drücken, um eine Liste der Elemente anzuzeigen, deren Namen mit dem bereits eingegebenen Teil übereinstimmen. Sie können dann das gewünschte Element aus der Liste auswählen, ohne den Rest des Namens eingeben zu müssen.

Wenn Sie in einer Datenbank arbeiten, die zahlreiche Objekte enthält, können die Listen der Befehlszeilenergänzung sehr umfangreich werden. Einige SQL Server-Objekttypen, z. B. Sichten, verfügen außerdem über zahlreiche Systemobjekte.

Die SQL Server-Snap-Ins führen drei Systemvariablen ein, mit denen Sie die von der Befehlszeilenergänzung und Get-ChildItem präsentierte Informationsmenge steuern können.

**$SqlServerMaximumTabCompletion =**n
Gibt die maximale Anzahl der Objekte an, die in der Befehlszeilenergänzungsliste enthalten sein sollen. Wenn Sie die TAB-TASTE an einem Pfadknoten betätigen, der mehr als n Objekte aufweist, wird die Befehlszeilenergänzungsliste bei n abgeschnitten. n ist eine ganze Zahl. 0 ist die Standardeinstellung und bedeutet, dass die Anzahl der aufgeführten Objekte nicht begrenzt ist.
**$SqlServerMaximumChildItems =**n
Gibt die maximale Anzahl der von Get-ChildItem angezeigten Objekte an. Wenn Sie Get-ChildItem an einem Pfadknoten ausführen, der mehr als n Objekte aufweist, wird die Befehlszeilenergänzungsliste bei n abgeschnitten. n ist eine ganze Zahl. 0 ist die Standardeinstellung und bedeutet, dass die Anzahl der aufgeführten Objekte nicht begrenzt ist.
$SqlServerIncludeSystemObjects = { $True | $False }
Wenn $True, werden Systemobjekte durch Befehlszeilenergänzung und Get-ChildItem angezeigt. Wenn $False, werden keine Systemobjekte angezeigt. Die Standardeinstellung ist $False.

Im folgenden Beispiel werden alle drei Variablen festgelegt und ihre Einstellungen aufgeführt:

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

Freigeben über