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.

I don't believe there is a standard task or anything close to it. This should get you started though. You'll have to query the status of the target copy in a loop: https://www.mssqltips.com/sqlservertip/2235/creating-backups-and-copies-of-your-sql-azure-databases/ — jessehouwing, Apr 17 '17 at 11:23

score 2 · Accepted Answer · edited May 23 '17 at 12:34

2

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

edited May 23 '17 at 12:34

Community

1
1

answered Apr 18 '17 at 03:19

starian chen-MSFT

33,174
2
29
53

thanks, this seems like a good option. Will try it out and report back. – Sanddy Apr 18 '17 at 10:00
not yet, I looked at creating a custom build task as you linked, but I am not very familiar with PowerShell and how to write the extension so that it also installs the Azure Powershell sdk along. I looked at the existing VS Build extensions in Git, but they seem to use quite a lot of internal Powershell libraries, nothing straight forward. So right now I am trying to experiment with Azure CLI, I have the CLI script working locally. But when I use the same script in an Inline Azure CLI task, it gives an "##[error]Unhandled: Failed which: Not found az: null" error! – Sanddy Apr 26 '17 at 10:11
I do see that I can upload a powershell or CLI script in my source control and then invoke it via the Release PowerShell task. But it does not feel right to have release scripts within my source control and having to make new push's to source control as release platform changes. There should be an independent way to upload and manage my release scripts rather than tie it up with my source code. I think currently, the only easy option is to go down this path. Any ideas? – Sanddy Apr 26 '17 at 12:14
@Sanddy The better way is put the script file in the source control, you can put it in other folder (TFVC) or other repository (git), then map or clone it to build agent (git clone command with Command line task). – starian chen-MSFT Apr 27 '17 at 02:23
1

I finally had to go the script way to create a PowerShell Release task and refer to a script supplied via Git. Linked is my Powershell script for reference (https://gist.github.com/saurabhnandu/ea474ab806d0159f1e58f82edcbea446 ) – Sanddy Apr 27 '17 at 13:43
@Sanddy You can custom build/release task if you have time, after that you don't need to put the script file to the source control. On the other hand, you can accept my solution as answer if it helps you to solve your issue or you can post an new solution and accept it as answer. – starian chen-MSFT Apr 28 '17 at 01:36

score 0 · Answer 2 · answered Apr 17 '17 at 16:29

Azure SQL Databases are continually backed up automatically. If you are trying to create a copy of the database or archive the database to a BACPAC file, you can do either.

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups https://learn.microsoft.com/en-us/azure/sql-database/sql-database-copy https://learn.microsoft.com/en-us/azure/sql-database/sql-database-export

Backup Azure SQL DB during VSTS Release

2 Answers2

Linked