AzureDW-GetLastAvailableGeoBackupDate.ps1

try
{
    # Built-in account
    $servicePrincipalConnection = Get-AutomationConnection -Name "AzureRunAsConnection"
    # Init variables (Automation Account variables)
    $ResourceGroup = Get-AutomationVariable -Name 'ResourceGroup'
    $SqlServerName = Get-AutomationVariable -Name 'SqlServerName'
    $DatabaseName = Get-AutomationVariable -Name 'DatabaseName'

    # Log into Azure with AzureRunAsConnection
    $null = Add-AzureRmAccount -ServicePrincipal `
                               -TenantId $servicePrincipalConnection.TenantId `
                               -ApplicationId $servicePrincipalConnection.ApplicationId `
                               -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 

    # Get GeoBackupPolicy State (Enabled?)
    $state = (Get-AzureRmSqlDatabaseGeoBackupPolicy -ResourceGroupName $ResourceGroup `
                                                    -ServerName $SqlServerName `
                                                    -DatabaseName $DatabaseName).State
    $state

    # Get LastAvailableBackupDate
    $lastAvailableBackupDate = (Get-AzureRmSqlDatabaseGeoBackup -ResourceGroupName $ResourceGroup `
                                                                -ServerName $SqlServerName `
                                                                -DatabaseName $DatabaseName).LastAvailableBackupDate
    $lastAvailableBackupDate
}
catch {
    throw $_.Exception
}