Background

Some CentreStack customers need a simple solution for backing up the SQL Server Express database on the CentreStack self-hosted server.

Solution

The attached .zip file contains a PowerShell script that can be used to backup the CentreStack database when CentreStack is using the local SQL Server Express instance. This is the default configuration for the "all in one server scenario". The script will:

1. Call the T-SQL "BACKUP DATABASE" command to do a full backup of the SQL Server Express database named "user".
2. The backup files will be saved in the C:\SQLBackup folder on the CentreStack server with a file name format: user_yyyy-MM-ddd_HH-mm-ss.bak (for example, "user_2018-04-26_13-24-42.bak")

Scheduled Task

If the script is executed with the -CreateTask command line parameter, the script will create a Windows Scheduled Task that will run the backup script every morning at 3 am. 

Script Content

What follows is the contents of the PowerShell script

<#
.SYNOPSIS
  Uses SQL BACKUP DATABASE command to backup the user database on a CentreStack server.
.DESCRIPTION
  Uses SQL BACKUP DATABASE command to backup the user database on a CentreStack server. May be executed
  with the -CreateTask argument to create a scheduled task that executes this script every day at 3 am.

.PARAMETER CreateTask

.EXAMPLE
  .\Backup-CentreStackSQLDB.ps1

.EXAMPLE
  .\Backup-CentreStackSQLDB.ps1 -CreateTask

#>
#Requires -Version 5 -RunAsAdministrator
param(
     [Parameter(Mandatory=$false)]
     [switch] $CreateTask
)
function New-BackupTask () {
    $powerShell = Join-Path $env:SystemRoot "System32\WindowsPowerShell\v1.0\powershell.exe"
    $taskTriggeraskAction = New-ScheduledTaskAction -Execute $powerShell -Argument "-NonInteractive -File $scriptFile"
    $taskTrigger = New-ScheduledTaskTrigger -Daily -At 3am
    $taskPrincipal = New-ScheduledTaskPrincipal -GroupId "BUILTIN\Administrators" -RunLevel Highest
    $taskSettings = New-ScheduledTaskSettingsSet
    $task = New-ScheduledTask -Action $taskTriggeraskAction -Principal $taskPrincipal -Trigger $taskTrigger -Settings $taskSettings
    Register-ScheduledTask -TaskName "Backup CentreStack Database Daily" -InputObject $task    
}

# *** SCRIPT BODY START ***

# Get this script
$thisScript = $Script:MyInvocation.MyCommand
# Get the directory of this script
$scriptDir = Split-Path $thisScript.Path -Parent
# Get the script file
$scriptFile = Get-Item $thisScript.Path
# Get the name of this script
$scriptName = $scriptFile.Name
# Get the name of the script less the extension
$scriptBaseName = $scriptFile.BaseName

# If the CreateTask argument is set then create the backup task
if ($CreateTask) {
    New-BackupTask
}

$backupFolder = 'C:\SQLBackup'
if (-not (Test-Path $backupFolder)) {
    New-Item -Path $backupFolder -ItemType Directory
}
$now = Get-Date -f "yyyy-MM-dd_HH-mm-ss"
$backupFile = ("user_{0}.bak" -f $now)
$backupPath = Join-Path $backupFolder $backupFile

$query = @"
BACKUP DATABASE [user] TO  DISK = N'$backupPath' WITH NOFORMAT, NOINIT,  NAME = N'user-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
"@
Invoke-Sqlcmd -ServerInstance "localhost\CENTRESTACK" -Query $query -querytimeout 0
if (Test-Path -Path $backupPath) {
    Write-Output "Backup of the CentreStack user database succeeded."
} 
else {
    Throw "The backup of the CentreStack user database failed."
}
# *** SCRIPT BODY END ***