Backs up Inmagic textbases, both SQL Server db and textbase files. Logs most recent backup to a file.

backup.ps1

<#
  Backs up Inmagic textbases, both SQL Server db and relevant textbase files.
  Peter Tyrrell, Andornot, www.andornot.com

  sqlps dependency
  If module sqlps does not exist, install from:
    Microsoft SQL Server 2016 Feature Pack (https://www.microsoft.com/en-us/download/details.aspx?id=52676)
    - SQLSysClrTypes.msi
    - SharedManagementObjects.msi
    - PowershellTools.msi
#>

param (
  [string]$server = "localhost",
  [HashTable]$databases = @{"Borrower" = "_InmTB_1"; "Catalog" = "_InmTB_2"; "Loans" = "_InmTB_3";},
  [string]$backupLocation = "c:\path\to\backup\destination",
  [string]$textbasesLocation = "c:\path\to\textbase\files",
  [array]$textbaseExtensions = @(".cac",".cba",".cbs"),
  [string]$logfile = "c:\path\to\backup.log"
)

Push-Location
Import-Module "sqlps" -DisableNameChecking
Pop-Location

# get script start time
$start = get-date

<#
  FUNCTIONS
#>

# write log lines to a file
function write-log {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)]
        [string]$msg,
        [Parameter(Mandatory=$false)]
        [ValidateSet("INFO","WARN","ERROR","DEBUG")]
        [string]$level = "INFO",
        [Parameter(Mandatory=$false)]
        [string]$file
    )

    $stamp = (get-date).ToString("yyyy/MM/dd HH:mm:ss")
    $line = "$stamp $level $msg"
    if ($file) {
        add-content $file -Value $line
    }

    # always write to console
    if ($level -eq "ERROR") {
      write-host $msg -ForegroundColor Red
    }
    elseif ($level -eq "WARN") {
      write-host $msg -ForegroundColor Yellow
    }
    else {
      echo $msg
    }
}

# back up SQL Server database to file
function backupTextbase ([string]$servername, [string]$db, [string]$outfile) {

  $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
  $srv.ConnectionContext.StatementTimeout = 0

  # test server instance is available
  try {
    $srv.databases | Out-Null
  }
  catch {
    write-log -level ERROR -msg "SQL Server $servername not available." -file $logfile
    return
  }

  try {
    # backup database to file
    Push-Location
    Backup-SqlDatabase -InputObject $srv -Database $db -BackupFile $outfile -BackupAction Database -CopyOnly -Initialize
    write-log -msg "Backed up $db to $outfile on $servername." -file $logfile
  }
  catch {
    write-log -level ERROR -msg $_.Exception.Message -file $logfile
  }
  finally {
    Pop-Location
  }
}

# return false if file path does not exist, or if file's LastWriteTime precedes provided date
function testFileCopy([string]$path, [datetime]$date) {
  $testpath = test-path -path $path -pathtype leaf
  if ($testpath -eq $false) {
    return $false
  }
  # is file LastWriteTime greater than provided date?
  $writetime = (Get-ChildItem $path).LastWriteTime
  echo "$writetime > $date ?"
  return (Get-ChildItem $path).LastWriteTime -gt $date
}

<#
  MAIN
#>

# new log file, overwrite existing
new-item $logfile -type file -force | out-null

foreach ($database in $databases.GetEnumerator()) {
    try {
      # sql backup
      $tb = $database.Key
      $file = join-path $backupLocation ($tb + ".dat")
      backupTextbase $server $database.Value $file

      # textbase file backup
      $globs = $textbaseExtensions | % {'*' + $_}
      copy-item -path ("{0}\$tb.*" -f $textbasesLocation.TrimEnd("\")) -include $globs -destination $backupLocation -force

      # check copied files exist and are newer than this script's start time
      foreach ($ext in $textbaseExtensions) {
        $copied = testFileCopy -path ("{0}\$tb$ext" -f $textbasesLocation.TrimEnd("\")) -date $start
        if ($copied -eq $false) {
          write-log -level WARN -msg "$tb$ext file copy check failed. External textbase files at $backupLocation may not be up to date." -file $logfile
        }
        else {
          write-log -level INFO -msg "$tb$ext copy tested OK." -file $logfile
        }
      }
    }
    catch {
      write-log -level ERROR -msg $_.Exception.Message -file $logfile
    }
}