Jun 28

Using Powershell to back up an SQL 2016 database and a file structure

I had a requirement recently to put together a backup that in one PowerShell script carried out a backup on both an SQL database and also a folder containing multiple pictures that were appended to on a daily basis.

I have broken down the following PowerShell script into sections to allow easier explanation of the component parts;

Firstly I needed to import the SQL PowerShell Components module to allow the scripting of the SQL commands.

Import-Module "sqlps" -DisableNameChecking

This is installed from the SQL 2016 media.

I then set up the scripting variable to make it easier to change after the script had been written.  I could have used parameters for this but decided against it as the variables weren’t going to change that often.  I will cover using parameters more extensively in a later post.

#Set Script Variables
$backupDrive = "E:"
$subfolder = "BackupSubFolder"
$logfolder = "$($backupDrive)\$($subfolder)"
$DBbfolder = "$($backupDrive)\$($subfolder)\DBBackup"
$filebfolder = "$($backupDrive)\$($subfolder)\SystemBackup"

As I was using an external USB hard drive, the first job was to get the system to check that the backup drive was actually plugged in and had registered a drive letter.

#function to check the drive exists
function Drive-Exists
{
  (New-Object System.IO.DriveInfo($backupDrive)).DriveType -ne 'NoRootDirectory'
}

The next step was to back up the database.  I wanted to use a unique date/time stamp for the filename so created a variable from the system time stamp using the “get-date” cmdlet;

#function to backup the database
function BackupDatabase
{
  $dt = get-date -format yyyyMMddHHmmss
  $bfil = "$($DBbfolder)\MyDB_db_$($dt).bak"
  Backup-SqlDatabase -InputObject 'MYSQLServer\MySQLInstance' -Database 'MyDatabase' -BackupFile $bfil | out-file $logfolder\backuplog.txt -Append
}

Please ensure that the instance in the -InputObject parameter reflects the instance to be backed up and the -Database parameter has a name that exists in the instance.

Now to pull it all together and add a little error checking;

#Start Live Script
if(Drive-Exists)
{
#Backup the SQL database
  if(Test-Path $DBbfolder)
  {
    BackupDatabase
  }
  else
  {
    Write-Host "The folder $($DBbfolder) does not exist on the $($backupDrive) drive"
  }
}
else
{
  Write-Host "Please plug in the USB drive and ensure it is identified as $($backupDrive)"
}

I use the Test-Path cmdlet to ensure the destination folder for the backup exists.  This is used twice in the script to test both the DB backup folder exists and also the file backup folder.

Finally I used Robocopy with the /MIR parameter, to backup the pictures to the external USB drive;

#Backup the data files (ROBOCOPY)
if(Test-Path $filebfolder)
{
  $fromPath = "C:\MyInitialLocation"
  $toPath = "$($filebfolder)\MyFinalLocation"
  Write-Host "Copying Files to drive $($backupDrive)"
  ROBOCOPY $fromPath $toPath /MIR /W:5 | out-file $logfolder\backuplog.txt -Append
  Write-Host "Backup successful"
  pause
  exit
}
else
{
  Write-Host "The folder $($filebfolder) does not exist on the $($backupDrive) drive"
}

#End Script

I have attached the complete script in a zip file below:

Click to download