Vista Customization – Download Azure SQL Backups

Scenario:
Want to have on-prem backup of Vista data for disaster recovery purposes.

Requirements:
None

Example Solution:

  1. Open a Viewpoint Support case with the following request:
    “We’d like to request an SAS Token to access the Azure Blob Container storage where our SQL backups reside. Please make the expiration date as far out as possible (ideally > one year).”
  2. Enter the Azure Blob Storage URL, the company code, and your destination folder into the script below.
  3. Save this PowerShell file on a server or PC.
  4. Use Windows Task Scheduler to schedule it to run nightly.
# Define your Azure Blob Storage SAS URL and container details
$sasUrl = "{sasUrl}"
$containerName = "{company_code}d1sqlbackup"
$downloadFolder = "\\{servername}\tc1_sql_backups"

# Extract the storage account name from the SAS URL
$uri = New-Object Uri($sasUrl)
$storageAccountName = $uri.Host.Split('.')[0]

# Create the storage context using SAS token
$context = New-AzStorageContext -StorageAccountName $storageAccountName -SasToken $uri.Query

# Get the current date and subtract one to pull the past day
$currentDate = Get-Date
$lastWeekDate = $currentDate.AddDays(-1)

# Function to download the new blobs
function Download-NewFiles {
    # List all blobs in the container
    $blobs = Get-AzStorageBlob -Container $containerName -Context $context

    foreach ($blob in $blobs) {
        $blobName = $blob.Name

        # Extract the date from the file name (assuming the date is the 4th segment in YYYYMMDD format)
        $dateSegment = ($blobName -split "_")[3] # Split by "_" and get the 4th part (0-indexed)

        # Check if the date segment matches the expected date format (YYYYMMDD)
        if ($dateSegment -match "^\d{8}$") {
            $fileDate = [datetime]::ParseExact($dateSegment, "yyyyMMdd", $null)

            # If the file date is within the last week, download it
            if ($fileDate -gt $lastWeekDate) {
                Write-Host "Downloading new file: $blobName (File Date: $fileDate)"

                # Prepare the local download path
                $downloadPath = Join-Path -Path $downloadFolder -ChildPath $blobName
                $directory = [System.IO.Path]::GetDirectoryName($downloadPath)

                # Create the directory if it doesn't exist
                if (-not (Test-Path -Path $directory)) {
                    New-Item -ItemType Directory -Path $directory -Force
                }

                # Download the blob to the local file system
                Get-AzStorageBlobContent -Container $containerName -Blob $blobName -Destination $downloadPath -Context $context
                Write-Host "Downloaded: $blobName to $downloadPath"
            } else {
                Write-Host "File $blobName (File Date: $fileDate) is older than one day."
            }
        } else {
            Write-Host "$blobName - Invalid Date Format in File Name"
        }
    }
}

# Call the function to download new files
Download-NewFiles

# ==========================================
# Clean up files older than 8 weeks
# ==========================================
Write-Host "`nCleaning up files older than 8 weeks in $downloadFolder..."

# Calculate threshold date (8 weeks = 56 days)
$thresholdDate = (Get-Date).AddDays(-56)

# Get a list of files older than 8 weeks
$oldFiles = Get-ChildItem -Path $downloadFolder -File -Recurse | Where-Object { $_.LastWriteTime -lt $thresholdDate }

foreach ($file in $oldFiles) {
    try {
        Write-Host "Removing file: $($file.FullName) (LastWriteTime: $($file.LastWriteTime))"
        Remove-Item -Path $file.FullName -Force
    }
    catch {
        Write-Warning "Failed to remove file $($file.FullName). Error: $($_.Exception.Message)"
    }
}