Backup Azure SQL DB during VSTS Release

Question

I am exploring VSTS Release Management and I wanted to backup my production database hosted on Azure SQL DB before I apply any migration scripts to it. I fail to find any particular task or preferred way of waiting till the Azure SQL DB is fully backed up so that I can proceed with deployment only after the database is correctly backed up.  I have looked at either using a PowerShell task or Azure SQL CMD task, but I am not sure how to make rest of the tasks wait for the backup to complete.  Would appreciate if anyone could point me in the right direction. Thanks. 

Answer 1

You can backup Azure SQL database and check the status in a loop.

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
   -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
   -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

$importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
[Console]::Write("Exporting")
while ($importStatus.Status -eq "InProgress")
{
    $importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
    [Console]::Write(".")
    Start-Sleep -s 10
}
[Console]::WriteLine("")
$importStatus

More information, you can refer to Export an Azure SQL database to a BACPAC file.

Another way is that you can backup Azure SQL database by call Microsoft.SqlServer.Dac.DacServices.ExportBacpac method with PowerShell.

param([string]$ConnectionString, [string]$DatabaseName,[string]$OutputFile,[string]$s)
Add-Type -Path "$s\AzureDatabaseSolution\SQLDatabase\lib\Microsoft.SqlServer.Dac.dll"
$now = $(Get-Date).ToString("HH:mm:ss")
$Services = new-object Microsoft.SqlServer.Dac.DacServices $ConnectionString
Write-Host "Starting at $now"  
$Watch = New-Object System.Diagnostics.StopWatch
$Watch.Start()
$Services.ExportBacpac($OutputFile, $DatabaseName)
$Watch.Stop()
Write-Host "Backup completed in" $Watch.Elapsed.ToString()  

Note: Using the assembly in this package: Microsoft.SqlServer.Dac 1.0.3 (I add it to the source control and map to build agent)

On the other hand, to add firewall rule, you can refer to this thread: Deploy Dacpac packages via power shell script to Azure SQL Server.

BTW, you can build the custom build/release step/task with these PowerShell scripts. Add a build task