Backup to azure Blob showing completed while backup still in progress

Question

I am using azure automation to automate the process to backup my database to azure blob storage, its working all fine but the issue i am trying to resolve is that the status shows completed before the actual backup is done, and if i try to rerun the automation it tells me this :

"ErrorActionPreference" or common parameter is set to Stop: 45183: There is an import or export operation in progress on the database 'database'.

Here is my powershell script:

    param(
    [parameter(Mandatory=$true)]
    [String] $ResourceGroupName,
    [parameter(Mandatory=$true)]
    [String] $DatabaseServerName,
    [parameter(Mandatory=$true)]
    [String]$DatabaseAdminUsername,
    [parameter(Mandatory=$true)]
    [String]$DatabaseAdminPassword,
    [parameter(Mandatory=$true)]
    [String]$DatabaseNames,
    [parameter(Mandatory=$true)]
    [String]$StorageAccountName,
    [parameter(Mandatory=$true)]
    [String]$BlobStorageEndpoint,
    [parameter(Mandatory=$true)]
    [String]$StorageKey,
    [parameter(Mandatory=$true)]
    [string]$BlobContainerName
    # [parameter(Mandatory=$true)]
    # [Int32]$RetentionDays
)

$ErrorActionPreference = 'stop'

function Login() {
    $connectionName = "AzureRunAsConnection"
    try
    {
        $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName         

        Write-Verbose "Logging in to Azure..." -Verbose

        Add-AzureRmAccount `
            -ServicePrincipal `
            -TenantId $servicePrincipalConnection.TenantId `
            -ApplicationId $servicePrincipalConnection.ApplicationId `
            -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint | Out-Null
    }
    catch {
        if (!$servicePrincipalConnection)
        {
            $ErrorMessage = "Connection $connectionName not found."
            throw $ErrorMessage
        } else{
            Write-Error -Message $_.Exception
            throw $_.Exception
        }
    }
}

function Create-Blob-Container([string]$blobContainerName, $storageContext) {
    Write-Verbose "Checking if blob container '$blobContainerName' already exists" -Verbose
    if (Get-AzureStorageContainer -ErrorAction "Stop" -Context $storageContext | Where-Object { $_.Name -eq $blobContainerName }) {
        Write-Verbose "Container '$blobContainerName' already exists" -Verbose
    } else {
        New-AzureStorageContainer -ErrorAction "Stop" -Name $blobContainerName -Permission Off -Context $storageContext
        Write-Verbose "Container '$blobContainerName' created" -Verbose
    }
}

function Export-To-Blob-Storage([string]$resourceGroupName, [string]$databaseServerName, [string]$databaseAdminUsername, [string]$databaseAdminPassword, [string[]]$databaseNames, [string]$storageKey, [string]$blobStorageEndpoint, [string]$blobContainerName) {
    Write-Verbose "Starting database export to databases '$databaseNames'" -Verbose
    $securePassword = ConvertTo-SecureString –String $databaseAdminPassword –AsPlainText -Force 
    $creds = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $databaseAdminUsername, $securePassword

    foreach ($databaseName in $databaseNames.Split(",").Trim()) {
        Write-Output "Creating request to backup database '$databaseName'"

        $bacpacFilename =$databaseName + "LiveBak_anon" + ".bacpac"
        $bacpacUri = $blobStorageEndpoint + "/" + $blobContainerName + "/" + $bacpacFilename

        $exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $resourceGroupName –ServerName $databaseServerName `
            –DatabaseName $databaseName –StorageKeytype "StorageAccessKey" –storageKey $storageKey -StorageUri $BacpacUri `
            –AdministratorLogin $creds.UserName –AdministratorLoginPassword $creds.Password -ErrorAction "Stop"
        
        # Print status of the export
        # Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink -ErrorAction "Stop"
        
    }
}

# function Delete-Old-Backups([int]$retentionDays, [string]$blobContainerName, $storageContext) {
#   Write-Output "Removing backups older than '$retentionDays' days from blob: '$blobContainerName'"
#   $isOldDate = [DateTime]::UtcNow.AddDays(-$retentionDays)
#   $blobs = Get-AzureStorageBlob -Container $blobContainerName -Context $storageContext
#   foreach ($blob in ($blobs | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq "BlockBlob" })) {
#       Write-Verbose ("Removing blob: " + $blob.Name) -Verbose
#       Remove-AzureStorageBlob -Blob $blob.Name -Container $blobContainerName -Context $storageContext
#   }
# }

Write-Verbose "Starting database backup" -Verbose

$StorageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageKey

Login

Create-Blob-Container `
    -blobContainerName $blobContainerName `
    -storageContext $storageContext
    
Export-To-Blob-Storage `
    -resourceGroupName $ResourceGroupName `
    -databaseServerName $DatabaseServerName `
    -databaseAdminUsername $DatabaseAdminUsername `
    -databaseAdminPassword $DatabaseAdminPassword `
    -databaseNames $DatabaseNames `
    -storageKey $StorageKey `
    -blobStorageEndpoint $BlobStorageEndpoint `
    -blobContainerName $BlobContainerName
    
# Delete-Old-Backups `
#   -retentionDays $RetentionDays `
#   -storageContext $StorageContext `
#   -blobContainerName $BlobContainerName
    
Write-Verbose "Database backup script finished" -Verbose

All i basically need is for this process to show running even while the backup operation is in progress because i am using this automation in a logic app.

Answer 1

You could use Get-AzureRmSqlDatabaseImportExportStatus

Sample :

PS C:\>Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink "https://management.contoso.com/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/resource01/providers/Microsoft.Sql/servers/server01/databases/database01/importExportOperationResults/00000000-000-0000-0000-000000000000?api-version=2014-04-01"
OperationStatusLink : 
ErrorMessage        : 
LastModifiedTime    : 4/15/2016 10:16:14 PM
QueuedTime          : 4/15/2016 10:16:13 PM
StatusMessage       : Running, Progress = 5.00 %
Status              : InProgress

When you run New-AzureRmSqlDatabaseExport, A job is submitted however it is never waited for the completion of the job. The subsequent lines gets executed.

In your code, you have mentioned the below line

Write-Verbose "Database backup script finished" -Verbose

Once the export job is submitted, the above line is executed.

To overcome this, you could use the Get-AzureRmSqlDatabaseImportExportStatus and poll the status, proceed on the completion status.