Strato database backup with PowerShell via SSH/SFTP

This blog runs on WordPress and is hosted by Strato. Although there are countless backup plugins for WordPress, I have not yet found one that meets my requirements. I only want to store a backup of the WordPress mySQL database on my NAS and not make my NAS publicly available on the Internet via FTP or SSH.

I have therefore created a small PowerShell script which is executed by my local server via Task Scheduler and connects to Strato via SSH and SFTP. First a backup of the database is created via SSH, which is then downloaded.

In principle, the script can also be used with any other hoster or mySQL server, if the server is accessible via SSH and SFTP, so I'll post the script here in a simplified form:

$sshserver = "ssh.strato.de"
$sshuser = "domain.de"
$sshpass = "MasterPassword"
$dbname = "DB1234567"
$dbhost = "rdbms.strato.de"
$dbuser = "U1234567"
$dbpass = "DatabasePassword"
$backuppath = "D:\Backup"
$sshpass = $sshpass | ConvertTo-SecureString -AsPlainText -Force
$Creds= New-Object System.Management.Automation.PSCredential -ArgumentList $sshuser, $sshpass
#Load Posh Module
try
{
Import-Module Posh-SSH
}
catch
{
write-host "Failed to load Posh"
}
#Create Database Backup
try
{
[string]$backupfilename = "$dbname" + "_" + (get-date -Format ddMMyyyy) + ".sql"
$sshsession = New-SSHSession -ComputerName $sshserver -Credential $creds -AcceptKey:$true
[string]$backupcmd = "mysqldump $dbname --add-drop-table -h $dbhost -u $dbuser -p$dbpass > $backupfilename"
$backupdb = Invoke-SSHCommand -Index $sshsession.index -Command "$backupcmd"
}
catch
{
write-host "Backup failed"
}
#Download Backup File
try
{
$sftpsession = New-SFTPSession -ComputerName $sshserver -Credential $creds
$backupfile = Get-SFTPFile -Index $sftpsession.index -RemoteFile $backupfilename -LocalPath $backuppath
}
catch
{
write-host "Backup download failed"
}
#Delete Backup File from webserver
try
{
$deletecmd = "rm " + "$backupfilename"
$deletefile = Invoke-SSHCommand -Index $sshsession.index -Command "$deletecmd"
}
catch
{
write-host "Could not delete Backup on webserver"
}
#Disconnect Session
try
{
$disconnect = remove-sshsession -index $sshsession.index
$disconnect = Remove-SFTPSession -Index $sftpsession.index
}
catch
{
write-host "Could not disconnect SSH/SFTP Session"
}
#Verify
try
{
test-path "$backuppath\$backupfilename"
}
catch
{
write-host "Verify failed"
}

Prerequisite for the script is the SSH Powershell module, which can be downloaded here:

• http://www.powershellmagazine.com/2014/07/03/posh-ssh-open-source-ssh-powershell-module/

The script can be easily extended, for example to send an error/success report by e-mail. In the above form, however, only the database backup is created and downloaded:

Maybe someone can use it...