Useful Backup Script

[nadim]

We've recently had to look closely at our backup process. We were mainly relying on the Microsoft Windows Backup for years. However, over time, our data got to be so large that our backups were rolling over nearly daily and we were basically unable to roll back to a sane copy when power outages caused damage to our disks and corrupted the data on them (we didn't realize this until too late unfortunately and suffered some data loss).

Since the incident we've created a scheduled task to separately backup our pworks/data folder on a daily basis. This task is run over night. It notifies us through a slack hook of status and stops the PSQL services before commencing, it then starts the services once backup is completed. (there is also an email notification should you not want to use slack). The script also hashes the DAT files and logs that into a file after the backup complete. Hashes of the files could help determine if they were tampered with or modified after the backup at some point, just for extra reassurance around the sanity of the files.  

# Define the source and destination directories and the log file path

# Force PowerShell to use TLS 1.2
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Function SendSlack($Message) {
    $SlackHook= "REPLACE_WITH_HOOK_FROM_YOUR_SLACK_WORKSPACE"
    $ContentType= 'application/json'

    $Body = @{
        "text" = $Message
    } | ConvertTo-Json

    Invoke-RestMethod -uri $SlackHook -Method Post -body $Body -ContentType $ContentType
}

# Variables
$SourceFolder = "C:\Pworks\data\" # Replace with the path to the data folder
$DestinationRootFolder = "REPLACE_WITH_A_BACKUP_LOCATION" #"D:\PworksDataBackups\"  #"\\SomeSystem\pwbackups\" # Replace with the path to the destination root folder
$CurrentDate = (Get-Date).ToString('MM-dd-yyyy')
$DestinationFolder = Join-Path -Path $DestinationRootFolder -ChildPath $CurrentDate
$LogFile = Join-Path -Path $DestinationRootFolder -ChildPath "BackupLog_$CurrentDate.txt"
$HashesFile = Join-Path -Path $DestinationRootFolder -ChildPath "FileHashes_$CurrentDate.txt"

# Email notification variables
$EmailTo = "REPLACE_WITH_NOTIFICATION_EMAIL" # Replace with your email
$CCTo = "REPALCE_WITH_CC_EMAIL"
$EmailFrom = "REPALCE_WITH_FROM_EMAIL" # Replace with a sending email
$SMTPServer = "REPALCE_WITH_SMTP_SERVER" # Replace with your SMTP server
$EmailSubject = "Pworks Data Backup Notification for $CurrentDate"
$stuff = ConvertTo-SecureString "REPALCE_WITH_PASSWORD" -AsPlainText -Force # Not password is in plaintext so anyone might read it if you don't protect this file properly
$Cred = New-Object System.Management.Automation.PSCredential ($EmailFrom, $stuff)
$SMTPPort = 587

$FailedBackup = 1
try 
{
    Stop-Service -Name "Pervasive.SQL (relational)"
    Stop-Service -Name "Pervasive.SQL (transactional)"
    # Create destination folder if it doesn't exist
    if (-not (Test-Path -Path $DestinationFolder)) {
        New-Item -ItemType Directory -Path $DestinationFolder -Force
    }
    $TotalSizeInBytes = (Get-ChildItem -Path $SourceFolder -Recurse | Measure-Object -Property Length -Sum).Sum
    $TotalSizeInGB = [math]::Round($TotalSizeInBytes / 1GB, 2)
    $TotalSizeInMB = [math]::Round($TotalSizeInBytes / 1MB, 2)
    Add-Content -Path $LogFile -Value "Copying $TotalSizeInGB GB ($TotalSizeInMB MB) worth of data"
    Write-Host "Copying $TotalSizeInGB GB ($TotalSizeInMB MB) worth of data" -ForegroundColor Red
    SendSlack("Backup process commenced Copying $TotalSizeInGB GB to $DestinationFolder")

    # Copy files and show progress
    $Progress = 0

    $FailedBackup = 0
    # Perform the backup (copy files)

    Copy-Item -Path $SourceFolder -Destination $DestinationFolder -Recurse -ErrorAction Stop
    # Log success message
    $SuccessMessage = "Backup on $CurrentDate to $DestinationFolder was successful. $TotalSizeInGB GB copied"
    Add-Content -Path $LogFile -Value $SuccessMessage
    Write-Host $SuccessMessage
    $FailedBackup = 0
} catch {
    # Log error message
    $ErrorMessage = "Backup on $CurrentDate to $DestinationFolder failed with error: $_"
    Add-Content -Path $LogFile -Value $ErrorMessage
    Write-Host $ErrorMessage -ForegroundColor Red
    $FailedBackup = 1
    SendSlack($ErrorMessage)
    Start-Service -Name "Pervasive.SQL (relational)"
    Start-Service -Name "Pervasive.SQL (transactional)"
}


# Log success message and send notification
If ($FailedBackup){
    $EmailSubject = "FAILED : $EmailSubject"
    $SuccessMessage = "Backup on $CurrentDate failed. $TotalSizeInGB GB attempted copy. $ErrorMessage"
 }
 else{
    $EmailSubject = "Success : $EmailSubject"
    $SuccessMessage = "Backup on $CurrentDate was successful. $TotalSizeInGB GB copied"
 }

Add-Content -Path $LogFile -Value $SuccessMessage
Write-Host $SuccessMessage

SendSlack($SuccessMessage)
# Email notification
$EmailBody = $SuccessMessage
try {
    Send-MailMessage -To $EmailTo -From $EmailFrom -CC $CCTo -Subject $EmailSubject -Body $EmailBody -SmtpServer $SMTPServer -Credential $Cred -UseSsl -Port 587
    Add-Content -Path $LogFile -Value "Email notification sent successfully on $CurrentDate."
} catch {
    Add-Content -Path $LogFile -Value "Failed to send email notification on $CurrentDate. Error: $_"
    Write-Host "Failed to send email notification. Error: $_" -ForegroundColor Red
    Start-Service -Name "Pervasive.SQL (relational)"
    Start-Service -Name "Pervasive.SQL (transactional)"
}

if($FailedBackup){
    Start-Service -Name "Pervasive.SQL (relational)"
    Start-Service -Name "Pervasive.SQL (transactional)"
    Exit 1
}
# Generating file hash list for .DAT files
Get-ChildItem -Path $DestinationFolder -Recurse -Filter *.DAT | 
    Where-Object { -not $_.PSIsContainer } | 
    Get-FileHash | 
    Out-File -FilePath $HashesFile

# Adding hash list generation status to the log file
if ($?) {
    $HashSuccessMessage = "File hash list for .DAT files generated successfully on $CurrentDate."
    Add-Content -Path $LogFile -Value $HashSuccessMessage
    Write-Host $HashSuccessMessage
} else {
    $HashFailureMessage = "File hash list generation for .DAT files failed on $CurrentDate."
    Add-Content -Path $LogFile -Value $HashFailureMessage
    Write-Host $HashFailureMessage -ForegroundColor Red
}

# Start the services
Start-Service -Name "Pervasive.SQL (relational)"
Start-Service -Name "Pervasive.SQL (transactional)"

# Wait for the services to start (with a timeout to avoid infinite loop)
$timeout = (Get-Date).AddMinutes(5)
while ((Get-Date) -lt $timeout -and 
       ((Get-Service -Name "Pervasive.SQL (relational)").Status -ne 'Running' -or 
        (Get-Service -Name "Pervasive.SQL (transactional)").Status -ne 'Running')) {
    Start-Sleep -Seconds 15
}

# Get the status of the services
$serviceStatusRelational = Get-Service -Name "Pervasive.SQL (relational)"
$serviceStatusTransactional = Get-Service -Name "Pervasive.SQL (transactional)"

# Create a message with the status of both services
$message = @"
Starting PSQL Services
Pervasive.SQL (relational): $($serviceStatusRelational.Status)
Pervasive.SQL (transactional): $($serviceStatusTransactional.Status)
"@

SendSlack($message)