DB Backup

Introduction

We must have AO database backup daily outside of GCP.  Backup will be stored to Veeam.

---

Export Production database

https://app.clickup.com/2496230/docs/2c5q6-69975/2c5q6-297475

• We are taking export of production database every day at night time at 03:00 (utc+3) | 02:00 (utc+2)
• After successfull export, we have powershell script in bckendsrvtst-0 which will execute powershell script with gcloud commands and copying db backup (.bak) to local disk.
  • Job start 05:00 AM
• Veeam agent will save the backup to veeam storage as a part of the file backup process.
• GCP AO File backups (https://app.clickup.com/2496230/docs/2c5q6-69975/2c5q6-324135)
  • Job start 05:30 AM

---

Script

# --- Settings ---
$DateStamp = Get-Date -Format "yyyy-MM-dd"
$LogFile   = "D:\db-backups\logs\ahola_db_backup_log_$DateStamp.txt"
$KeyFile   = "D:\db-backups\db-backup-service-account.json"
$Source    = "gs://scheduled-db-export/daily-backups/AholaLiveBackupToTest.bak"
$Dest      = "D:\db-backups\ahola_transport\"

# --- Logger Function ---
function Write-Log {
    param([string]$Message)
    $Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $LogEntry = "[$Timestamp] $Message"
    $logDir = Split-Path $LogFile
    if (!(Test-Path $logDir)) { New-Item -ItemType Directory -Path $logDir -Force | Out-Null }
    Add-Content -Path $LogFile -Value $LogEntry
    Write-Host $LogEntry
}

try {
    Write-Log "=========================================="
    Write-Log "RUNNING BACKUP FOR DATE: $DateStamp"

    if (!(Test-Path $Dest)) { throw "Destination path $Dest not found." }

    # 1. Activate Service Account
    Write-Log "Step 1: Activating Service Account..."
    & gcloud auth activate-service-account --key-file="$KeyFile" 2>&1 | Out-String | Write-Log

# 2. Copy DB backup and Calculate Stats Manually
    Write-Log "Step 2: Copying file and calculating throughput..."

    $StartTime = Get-Date

    # Run the copy without the problematic flag
    & gcloud storage cp "$Source" "$Dest" --quiet 2>&1 | Out-String | Write-Log

    if ($LASTEXITCODE -eq 0) {
        $EndTime = Get-Date

        # Get the file size of the downloaded file
        $LocalFile = Join-Path $Dest (Split-Path $Source -Leaf)
        $FileSizeBytes = (Get-Item $LocalFile).Length
        $FileSizeGB = [Math]::Round($FileSizeBytes / 1GB, 2)

        # Calculate Duration and Throughput
        $Duration = $EndTime - $StartTime
        $TotalSeconds = $Duration.TotalSeconds
        if ($TotalSeconds -gt 0) {
            $SpeedMBps = [Math]::Round(($FileSizeBytes / 1MB) / $TotalSeconds, 2)
        } else {
            $SpeedMBps = 0
        }

        Write-Log "TRANSFER SUMMARY:"
        Write-Log " - Total Size: $FileSizeGB GB"
        Write-Log " - Time Taken: $($Duration.Minutes)m $($Duration.Seconds)s"
        Write-Log " - Average Speed: $SpeedMBps MB/s"
        Write-Log "RESULT: Success."
    } else {
        throw "gcloud exited with error code $LASTEXITCODE"
    }
}
catch {
    Write-Log "RESULT: !!! ERROR !!! Details: $($_.Exception.Message)"
}
Write-Log "PROCESS END"