Powershell meets SQL Server – Backup einer Remote Datenbank

Auf dem SQLSaturday #525 in St. Augustin hatte ich bereits das Vergnügen Andre Kamann zu lauschen, was er alles mit Powershell und dem SQL Server anstellt, meinen zweiten Kontakt zu Andre hatte ich jetzt beim SQLGrillen in Lingen, bei dem er uns die wesentliche Vereinfachung eines Rollouts des Ola Hallengren Skriptes näher brachte. Irgendwie hat mich diese „Vereinfachung“ inspiriert… ich bin eigentlich auch eher ein „fauler Hund“ und versuche mir mit Tools, Skripten  oder eigenen unterstützenden Webseiten das DBA Leben zu erleichtern. Heute kam dann ein Ticket zu mir, bei dem es um ein einfaches Backup einer Datenbank ging, der Kunde hat Probleme mit seiner Applikation und möchte nun eine Kopie der Datenbank an den Software-Hersteller schicken. Solche Aufgaben haben wir immer wieder, daher dachte ich heute morgen an Andre und seine Powershell-Skripte.
Viele werden jetzt sagen, so ein Backup ist doch recht einfach => entweder auf den Server per RDP oder von seiner Workstation mit dem SQL Server Management Studio verbinden, dann die Datenbank auswählen und mit der rechten Maus „Tasks => Backup => …“ Klar das geht, ABER da wir hier meistens über Citrix Steppingsstones oder Admingates in die Kundennetze müssen, erleichtert es uns die Arbeit schon um einiges, wenn wir den einen oder anderen Hop reduzieren können.

Mein Powershell-Template

Da dies meine ersten Schritte mit Powershell-Skripten sind, musste ich mich erst damit auseinandersetzen, wie man am besten ein solches Powershell Skript strukturell aufbaut. Hierzu habe ich auf diesen Powershell Blogs gelesen und keinen „Standard“ ausfindig machen können, aber überall konnte ich mir ein wenig abschauen und somit einen „ersten Wurf“ eines Powershell Skript Templates erstellen. Natürlich muss sich dieser noch in der täglichen Arbeit bewähren und eventuell angepasst werden, aber erst einmal kann ich (denke ich zumindest) damit arbeiten.

Angefangen habe ich natürlich mit einem Header

# #############################################################################
# DBA - SCRIPT - POWERSHELL
# NAME: template.ps1
# 
# AUTHOR: Björn Peters, SQL-aus-Hamburg.de
# DATE: 2016/08/24
# EMAIL: info@sql-aus-hamburg.de
# 
# COMMENT: This script will....
#
# VERSION HISTORY
# 1.0 2016.08.24 Initial Version.
# 
#
# OPEN POINTS TO ADD
# -Add a Function to ...
# -Fix the...
# 
# #############################################################################

Aus dem Tagesgeschäft weiß ich bzw habe ich die Erkenntnis gezogen, dass es für den DBA/Ausführenden am einfachsten ist, wenn die ggfs manuell zu konfigurierenden Parameter (wenn sie nicht übergeben werden) ganz oben stehen sollten, damit man sie möglichst schnell findet und einfach editieren kann. Also kommt direkt in meinem Powershell Skript Template nach dem Header ein Abschnitt zur Konfiguration des Skriptes.

# --- CONFIG ---#
# Script Path/Directories
$ScriptPath = (Split-Path ((Get-Variable MyInvocation).Value).MyCommand.Path)
$ScriptPluginPath = $ScriptPath + "\plugin\"
$ScriptToolsPath = $ScriptPath + "\tools\"
$ScriptOutputPath = $ScriptPath + "\Output\"
# Date Format
$DateFormat = Get-Date -Format "ddMMyyyy_HHmmss"
# -- END CONFIG ---#

Jetzt kommen wir zu einem der wichtigsten, aber am meisten vernachlässigten Abschnitten eines solchen Powershell Skriptes… der Hilfe.
Bei meinen Recherchen bin ich immer wieder über den Widerspruch gestolpert, dass man auf der einen Seite möglichst sein Skript kurz und übersichtlich gestalten soll, auf der anderen Seite aber gut dokumentiert und erläutert. Natürlich kann man aus einem Skript mehrere Versionen machen, eine zum Ausführen und eine zum Weitergeben… aber das führt meist zu Fehlern da man garantiert nicht immer alle Änderungen in beiden Daten ausführt => also bitte gleich richtig machen und einen Hilfe-Abschnitt integrieren und ausführlich beschreiben.

# --- HELP ---#
<#
.SYNOPSIS
Cmdlet help is awesome.

.DESCRIPTION
This Script does a ton of beautiful things!

.PARAMETER

.INPUTS

.OUTPUTS

.EXAMPLE

.LINK
https://www.sql-aus-hamburg.de
#>
# --- END HELP ---#

Zu guter letzt bleiben nur noch die Abschnitte für die einzelnen Funktionen und das eigentliche Powershell Skript. Früher (zu meinen VB / VBA Zeiten) hatte ich gelernt, dass man die Funktionen immer nach unten schreibt und den eigentlichen Programmcode nach oben. Aber dies scheint (zumindest für Powershell) mittlerweile überholt zu sein, also kommen die Funktionsaufrufe, die eigentliche Logik des Skriptes nach unten.

# --- FUNCTIONS ---#
Param(

)

BEGIN {

}

PROCESS {

}

END {

}
# --- END FUNCTIONS ---#

# --- SCRIPT ---#

Quellen und großen Dank an :
http://www.lazywinadmin.com/2012/03/powershell-my-script-template.html
Bei Francois habe ich die meisten Dinge (nachdem ich sie auch anderweitig unter „Best Practices“ für Powershell Skripte gelesen hatte) in einem Template gefunden und für mich am besten geeignet befunden. Also habe ich sein Template nahezu 1:1 übernommen.

Mein erstes Powershell Skript

Nun zum eigentlichen SQL Server Backup Skript, welches ich mit Powershell umsetzen wollte.
Also ich wollte mit „Bordmitteln“ von einer zentralen Maschine ein Backup auf einem Remote SQL Server erstellen, welches ich dann „einfach“ für den Kunden oder Drittdienstleister kopieren und bereitstellen konnte. Was liefert also die SQL Server Installation von Haus aus für Möglichkeiten? Natürlich die SQL Server Management Objects (SMO), welche im Rahmen der Management Tools Installation mitinstalliert werden .

Welche Parameter möchte ich denn angeben bzw übergeben, damit mein Backup erfolgreich läuft…

• Servername
• Instanzname
• Datenbankname
• SQL-Login => Username/Passwort

Nach einigem Nachdenken kamen noch zwei weitere Parameter dazu… => CopyOnly (True/False) und OpenInExplorer (True/False)

Param(
          [Parameter(Mandatory=$true)][string]$ServerName,
          [string]$InstanceName,
          [Parameter(Mandatory=$true)][string]$DatabaseName,
          [string]$SQLUserName,
          [string]$SQLPwd,
          [switch]$CopyOnly = $True,
          [switch]$OpenInExplorer = $False
)

Also BEGIN der Funktion

Erst einmal alle Parameter überpüfen, zusammensetzen und ggfs umsetzen, so dass sie im Skript bzw T-SQL Befehl verwendet werden können.
Vielleicht kann man das optimaler/sicherer gestalten (Anmerkungen/Tips hierzu bitte über die Kommentarfunktion), aber ich wollte ja einen schnellen Erfolg. Zu Beginn der Funktion prüfe ich also, ob es sich um eine Named-Instance oder Default-Instance handelt, wie der User sich anmelden möchte => Windows Authentifizierung oder SQL Login, on das Backup als Copy-Only-Backup erstellt werden soll und natürlich den Zielort des Backups (Default Backup Directory).

BEGIN {
	"Starting Backup on $($ServerName)"
        
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null
		
        $CommonParameters = ''

        if ($InstanceName) {
	    $SQLServer = $ServerName + "\" + $InstanceName
        } else {
            $SQLServer = $ServerName
        }

        $SQLSvr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $SQLServer
        if ($SQLUserName -and $SQLPwd) {
            $SQLSvr.ConnectionContext.LoginSecure=$false;
            $SQLSvr.ConnectionContext.Login=$SQLUserName
            $SQLSvr.ConnectionContext.Password=$SQLPwd
        }

	$BackupDir = $SQLSvr.Settings.BackupDirectory "BackupName - $($BackupDir)$($DatabaseName)_db_$($DateFormat).bak"

        if ($CopyOnly -eq $True) { $CommonParameters += " -CopyOnly" }

}

Nun kommt der PROCESS

Wie der Name schon sagt, hier geschieht der eigentliche Prozess, also die eigentlich Hauparbeit… Im Falle eines Backup-Skriptes werden hier die Backup Befehle ausgeführt. Da dies mein Einstieg in die Powershell Programmierung ist, es gibt bestimmt Wege dies anders bzw optimaler zu gestalten, aber für mich funktionierte das erst einmal ganz gut so. Wenn also der String $CommonParameters leer oder Null ist wird der einfache Backup Befehl ausgeführt ansonsten werden die WITH-Parameter an den Backup Befehl angehängt. Ansonsten passiert in diesem PROCESS Schritt nicht viel 😉

PROCESS {
        IF([string]::IsNullOrWhiteSpace($CommonParameters)) {
            Backup-SqlDatabase -InputObject $SqlServer -Database $DatabaseName -BackupFile "$($BackupDir)$($DatabaseName)_db_$($DateFormat).bak"
        } else {
		    Backup-SqlDatabase -InputObject $SqlServer -Database $DatabaseName -BackupFile "$($BackupDir)$($DatabaseName)_db_$($DateFormat).bak" $CommonParameters
        }
	}

Alles hat ein ENDe

Eigentlich wäre hier nun alles zu Ende, wir haben ein Backup auf einem Remote SQL Server in das Default Backup Verzeichnis geschrieben… hier könnten wir es abholen und dem Anforder zur Verfügung stellen. Aber ich bin ein wenig faul… also was liegt näher als den letzten Schritt noch etwas zu vereinfachen.
Also habe ich in den END Abschnitt einfach einen Explorer Aufruf mit dem Zielpfad (Default Backup Verzeichnis) hinzugefügt, so muss ich nicht erst lange suchen und hin und her klicken, sondern der Explorer öffnet sich direkt mit dem Zielpfad und ich brauche die Datei nur noch weg zu kopieren. 😉

END {
        "Finished Backup on $($ServerName)"
        if ($OpenInExplorer -eq $True) { 
            $NewBackupDir = "\$($ServerName)\"
            $NewBackupDir += $BackupDir.Replace(':\', '$\')
            Invoke-Item $NewBackupDir 
        }
	}
}
# --- END FUNCTIONS ---#

Zum Abschluss im Skript muss natürlich noch der ganze Ablauf einmal gestartet werden, also rufe ich als „letzte“ Zeile des Skriptes schnell die Funktion auf… FERTIG.

Vielen Dank nochmal an Andre für die Inspiration.