Vista Customization – Download Azure SQL Backups

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

Requirements:
None

Example Solution:

2026 Update: We’ve been told that you can provide this access yourself via Cascade Admin portal look for the “{COMPANY CODE}-sqlbackup-reader” group, assign a user, then use that user to log in via Azure Storage Explorer. In this case, have AI assist you in modifying the code below to utilize your account in lieu of the SAS token.

  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)"
    }
}