📖💡 Befehlszeilenanwendung

Das Dienstprogramm sqlcmd

Um eine Verbindung zu SQL Server herzustellen, ist nicht unbedingt eine grafische Oberfläche wie das Management Studio nötig, sondern es können dafür genauso Dienstprogramme von der Kommandozeile aus benutzt werden. Ein solches Dienstprogramm ist die Befehlszeilenanwendung sqlcmd. Dieses Tool ist vor allem bei regelmässig auszuführenden Datenbank- bzw. Wartungstasks sehr nützlich und hilfreich.

sqlcmd kann in drei Modi ausgeführt werden:

SQLCMD-Modus im SSMS (SQL Server Management Studio)

In diesem Modus können über die grafische Oberfläche des SSMS entweder sqlcmd- oder T-SQL-Anweisungen eingegeben werden. Kennzeichnend ist, dass quasi vor jedem sqlcmd-Befehl ein : steht. Ein :!! bedeutet, dass ein Befehl in der Windows Command-Shell ausgeführt wird. Oft verwendete Befehle sind:

• :help zeigt Liste der Befehle

• :connect server[\instance] [-U user [-P password]] stellt Verbindung her

• :out lenkt den Output einer Anweisung in ein File z.B.

• :setvar belegt oder löscht eine sqlcmd Variable

• :exit verlässt sqlcmd unmittelbar

sqlcmd im interaktiven Modus

In diesem Modus wird ausschliesslich über die Eingabeaufforderung gearbeitet und werden vor allem ad hoc-Queries abgesetzt. Welche Parameter mit diesem Programm verwendet werden können und was diese bedeuten, kann über die Eingabeaufforderung mit -? angezeigt werden. Bei der Angabe der Parameter wird zwischen Gross- und Kleinschreibung unterschieden. Ohne Angabe eines Parameters baut sqlcmd eine vertraute Verbindung zum lokalen Server auf. Eine erfolgreich hergestellte Verbindung wird dadurch deutlich, dass eine Zeilennummer am Anfang jeder Zeile eingeblendet wird.

sqlcmd im Command-Modus

Dieser Modus bietet für einen DBA den wahrscheinlich grössten Nutzen. In diesem Modus wird ebenfalls über die Eingabeaufforderung ein kompletter Befehl abgesetzt und mit ENTER ausgeführt. So kann beispielsweise mit einem Befehl eine Verbindung zu einer Instanz hergestellt, eine Abfrage ausgeführt und deren Ergebnis in eine Datei geschrieben werden.

SQL Server-Integration in die Windows PowerShell

Windows PowerShell-Befehle können als wertvolle Ergänzung zum SQL Server Management Studio angesehen werden. Die Windows PowerShell kann nicht nur T-SQL-Befehle ausführen, sondern stellt darüber hinaus eine mächtige Umgebung zur Skripterstellung dar, in welcher eine objektorientierte, auf dem .NET Framework basierende Skriptsprache zur Verfügung gestellt wird. Ein Vorteil der PowerShell-Umgebung ist, dass mit dieser in einer Windows- Infrastruktur verschiedene Server verwaltet werden können: Windows, Exchange, SharePoint, SQL Server, etc.

Das Dienstprogramm sqlps

Damit mit der Windows PowerShell auf SQL Server zugegriffen werden kann, liefert Microsoft das Dienstprogramm sqlps mit. Bei diesem Programm handelt es sich um eine speziell für die Verwendung mit SQL Server angepasste PowerShell-Oberfläche (quasi eine PowerShell mit geladenen SQL-Erweiterungen), in welcher – ähnlich wie in einem Dateisystem – in der Verzeichnisstruktur des SQLSERVER-Laufwerks navigiert und eine bestimmte Aktion ausgeführt werden kann. Zudem kann sqlps für Folgendes verwendet werden:

• - Interaktives Ausführen von Windows PowerShell-Befehlen

• - Ausführen von Windows PowerShell-Skriptdateien

• - Ausführen von SQL Server-Cmdlets

Cmdlets: vordefinierte PowerShell-Skripts

Da in der PowerShell kein entsprechender Laufwerksbefehl zum Anzeigen eines Tabelleninhalts resp. überhaupt zum Ausführen von SQL-Befehlen existiert, muss hierfür ein sogenanntes SQL Server-Cmdlet verwendet werden: in diesem Fall das Invoke-Sqlcmd. Dieses spezifische Cmdlet ruft das Dienstprogramm sqlcmd auf und dient dazu, SQL-Anweisungen auszuführen. Das Cmdlet wird in der Regel vor die SQL- Direktive gesetzt. Cmdlets werden mit dem sqlps-Module geladen und können als vordefinierte Skripte angesehen werden, die einem bestimmten Zweck dienen. Neben dem Invoke-Sqlcmd gibt es noch eine Reihe weiterer Cmdlets wie z.B.:

• Get-Command

• Get-Help

• Backup-SqlDatabase

• Restore-SqlDatabase

• Get-SqlCredential

• Get-SqlDatabase

💡Aufgaben

--------------------------------------------------------
-- SQLCMD-Modus im SSMS: siehe Datei SQLCMD_SSMS.sql
--------------------------------------------------------

--------------------------------------------------------
-- interaktiver Modus: vor allem fuer ad hoc Queries
--------------------------------------------------------
-- Anzeige der Hilfe
sqlcmd -?

-- Vertraute Verbindung zum lokalen Server
sqlcmd

USE northwind;

SELECT * FROM employees;

-- Die Ausfuehrung der Anweisung wird ueber GO + Enter gestartet
GO[n] Die Anweisung wird n-mal ausgefuehrt

/* Aufgabe 
Verbindung aufbauen (SQL Server Authentifizierung) 
und alle Kundennamen mit Angabe von Ort und Land in ein Text-File ausgeben
*/

--------------------------------------------------------
-- Command Modus: kompletter Befehl wird abgesetzt und mit ENTER ausgefuehrt
--------------------------------------------------------
-- Beispiele
sqlcmd -S RRJ -d Northwind -Q "SELECT FirstName, LastName FROM dbo.employees"

sqlcmd -S RRJ -d Northwind -i G:\Documents\SQL\Queries\EmployeeQuery.sql -o G:\Documents\SQL\Output\Employees.txt

-- Beispiel fuer SQL-File mit Variabeln
SELECT FirstName, LastName 
FROM dbo.employees 
WHERE employeeID > '$(id)'
  AND city = '$(city)'

sqlcmd -S RRJ -d Northwind -i G:\Documents\SQL\Queries\EmployeeQuery_V.sql -v id="2" city="London" -o G:\Documents\SQL\Output\Employees.txt

/* Aufgabe 
Aufgabe von oben in Command-Modus schreiben
*/

--------------------------------------------------------
-- Windows PowerShell:
--------------------------------------------------------
-- Powershell ISE als grafische Oberflaeche mit mehr Funktionen und fuer komfortableres Arbeiten mit der Skriptsprache
-- Als Einstieg beide Oberflaechen oeffnen und Beispielbefehle absetzen:
-- Pruefen, ob das Module vorhanden ist
Get-Module -ListAvailable -Name Sqlps;
-- Sqlps-Module importieren. 
Import-Module Sqlps -DisableNameChecking;
-- Hierfuer muessen auf dem System Skripte ausgefuehrt werden duerfen.
-- Dies wuerde wie folgt aktiviert werden:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Set-ExecutionPolicy Undefined

-- Im folgenden wird PowerShell aus SSMS gestartet und verwendet.
-- Im Kontext von SSMS ist dieses Modul automatisch geladen.
-- PowerShell parallel dazu aus SSMS starten
Geladene Module: get-module
Verfuegbare Module: get-module -ListAvailable -Name SQL*
Cmdlets: get-command -module SQLSERVER

get-psdrive listet die Rechner-Bereiche (Laufwerke) wie in einem Dateisystem auf:
Teile der Registry, physische Laufwerke, Zertifikate, SQL Server, etc.


mit get-psdrive SQLSERVER-Laufwerk durchlaufen zu
Die Datenbank Northwind in NW umbenennen und zurueck:
PS SQLSERVER:\SQL\RRJ\DEFAULT\Databases>
ren Northwind NW
ren NW Northwind


Tabellen anschauen:
PS SQLSERVER:\SQL\RRJ\DEFAULT\Databases\Northwind\tables>
cd Northwind\Tables
dir

Tabelleninhalt ausgeben:
PS SQLSERVER:\SQL\RRJ\DEFAULT\Databases\Northwind>
Verfuegbare Cmdlets: get-command -module SQLSERVER
Invoke-Sqlcmd -Query "SELECT * from employees;"
Invoke-Sqlcmd "SELECT lastname, firstname, email, password from employees;"
Invoke-Sqlcmd "SELECT * from categories;"
Invoke-Sqlcmd -Query "SELECT @@VERSION;"
-- Provider ist die SQL PowerShell

-- Systemzeit anzeigen
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "RRJ"

-- Variable definieren und in Abfrage einbinden
$name = "Buchanan"
-- Ausgabe der Variable
write-host $name
-- Variable in Abfrage einbinden
Invoke-Sqlcmd -Query "SELECT * from employees where lastname = '$($name)';"

-- Hilfeseite mit Beispielen zu SQL-Abfragen 
Get-Help Invoke-Sqlcmd -Examples

=========================================================
-- Use Case: Datenbank sichern
-- Mit der Express-Version ist der SQL Server Agent nur eingeschraenkt verfuegbar.
-- Mit Powershell laesst sich eine DB trotzdem relativ einfach sichern
-- Noetig sind ein kleines Powershell-Skript und den Aufgabenplaner von Windows

-- Direktes Ausfuehren des Skripts im Verzeichnis c:\temp: .\BackupDB.ps1

-- Im Aufgabenplaner Aufgabe erstellen, im Reiter Aktionen > Neu > Programm starten waehlen,
-- ..\WindowsPowerShell\v1.0\powershell.exe waehlen und bei den Argumenten den vollen Pfad
-- inkl. Skriptname eintragen. Abschliessend Trigger (Zeitplan) definieren.