Powershell script to ftp MySQL backup file and restore it locally.

The following Powershell script can be customised and scheduled (

 powershell.exe -executionpolicy bypass -file E:\scripts\dbrestore.ps1
) on your server:

 <#########################################################################
  Purpose:  FTP down MySQL backup file and restore it locally.
  Requirements: 1) Source backup file automatically created daily.
       The name is generated as YYYYMMDD_dbname.sql
       and located in /dbbackup folder.
      2) Indy.Sockets.dll exits on the destination system.
       Lets presume that it is located in e:\scripts
 #########################################################################>
 function Open-FTPConnection($ftphost, $username, $password) {
  [void][Reflection.Assembly]::LoadFrom("e:\scripts\Indy.Sockets.dll");
   $ftp = new-object Indy.Sockets.FTP;
   $ftp.Disconnect();
   $ftp.Host = $ftphost;
   $ftp.Username = $username;
   $ftp.Password = $password;
   $ftp.Connect();
   $ftp.Passive=$true;
   return $ftp;
  }

 function Close-FTPConnection($ftp) {
  $ftp.Disconnect();
  }

 function Draw-DirectoryContents($ftp) {
  "Listing directory contents ..";
  $ls = new-object System.Collections.Specialized.StringCollection;
  $ftp.List($ls, "", $true);
  foreach($file in $ls){
   $file;
   }
   Get-FTPCurrentLocation $ftp
  "Complete";
  }

 function Get-FTPCurrentLocation($ftp) {
  return $ftp.RetrieveCurrentDir();
  }

 function Set-ParentLocation($ftp) {
  "Changing to Parent Directory ..";
  $ftp.ChangeDirUp();
  Draw-DirectoryContents;
  }

 function Download-FTPFile($ftp, $sourceFileName, $targetDir) {
  "Downloading {0} into {1}.." -f $sourceFileName, $targetDir;
  $ftp.Get($sourceFileName, ($targetDir + $sourceFileName), $true, $false);
  "Downloading of {0} into {1} is complete" -f $sourceFileName,
  [Environment]::CurrentDirectory;
  }
  
 function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {
  "Uploading {0} into {1}.." -f $sourceFileName, $targetDir;
  $ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);
  "Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir;
  }
 
 function Process-FTPItem($ftp, [string]$sel) {
   [string[]]$fields=[Regex]::Split($sel, " +");
   $startField=8; #the file/directory name starts after 8 fields
   [string]$name="";
  
  for ($field=$startField; $field -lt $fields.Length; $field++) {
    if ($field -eq $startField) {
    $temp = "";
    } else {
    $temp = " ";
    }
    $name += $temp + $fields[$field];
    #add aditional space for name split into multiple fields
   }
  
  if ($sel[0] -eq 'd') { #directory
    "Changing directoy to {0} .." -f $name;
    $ftp.ChangeDir($name);
    Draw-DirectoryContents $ftp #redraw contents after changing directory
  } else {
    if ($sel[0] -eq '-') { #plain file has '-' as first character
    return $name; #return the name of file to download
    }
  }
  }
 
 function datetime{
  $year = (Get-Date).Year;
  $month = Get-Date -format MM;
  $day = Get-Date -format dd;
  $hour = Get-Date -format hh;
  $minute = Get-Date -format mm;
  $second = Get-Date -format ss;
  return "$day/$month/$year $hour`:$minute`:$second";
 }

 $year = (Get-Date).Year;
 $month = Get-Date -format MM;
 $day = Get-Date -format dd;
 $hour = Get-Date -format hh;
 $minute = Get-Date -format mm;
 $second = Get-Date -format ss;
 CLEAR-HOST
 $Host.UI.RawUI.ForegroundColor = "yellow"
 [void][reflection.assembly]::LoadWithPartialName("microsoft.visualbasic")
 $date = datetime;
 "** Start process $date **";
 write-host "`r";
 $theDate = "$year$month$day";
 $word_to_delete = "_dbname.sql";
 $file = "/dbbackup/$theDate$word_to_delete";
 $dir = "e:\db_backup"; #Destination folder
 $from = "$dir\dbbackup";
 $to = "$dir\$theDate`_$hour$minute$second";
 $out = "$to\$theDate$word_to_delete";
 $procedures = "/dbbackup/curls_procedures_$year-$month-$day.sql"
 if (!(Test-Path -path "$dir\$theDate*")){
  if ((Test-Path -path "$dir\dbbackup")){
   Remove-Item "$dir\dbbackup";
  }
  New-Item "$dir\dbbackup" -type directory;
  $f = Open-FTPConnection "www.sitename.com" "ftpUserName" "ftpPassword";
  Download-FTPFile $f $file ($dir);
  Download-FTPFile $f $procedures ($dir);
  Close-FTPConnection $f;
  Rename-Item $from $to;
  #Global replace of the keyword
  (Get-Content $out) | Foreach-Object {$_ -replace "sourceDBname", "destinationDBname"} | Set-Content $out;
  Write-Host $to;
  Write-Host $from;
 } else {
  "File already downloaded!";
  "Processing local restore ...";
  $items = Get-ChildItem -Path "$dir\$theDate*"
  foreach ($item in $items){
   $val = $item.Attributes
     if ($item.Attributes -Like "Directory*"){
    $theDir = $item.Name
     }
  }
  $theDir = "$dir\$theDir"
  $fileEntries = [IO.Directory]::GetFiles($theDir); 
  foreach($filename in $fileEntries){ 
   $file = $filename;
  }
 }

 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname --execute=""SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(TABLE_NAME),';') AS stmt FROM information_schema.TABLES WHERE  TABLE_SCHEMA = 'destinationDBname' INTO OUTFILE 'e:\\db_backup\\dropAllTables.sql'"""
 Write-Host $mysql;
 & cmd /c $mysql;
 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < e:\db_backup\dropAllTables.sql"
 Write-Host $mysql;
 & cmd /c $mysql;
 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < $out"
 Write-Host $mysql;
 write-host "`r";
 $date = datetime;
 "** End of process $date **";

If you find this script useful please donate generously.