Generate a bacpac for Azure SQL Database

There are several ways to transfer a bacpac from an Azure SQL Database to storage. However, you should take some precautions to ensure the export is transactionally consistent.

Transactionally Consistent

One thing not to forget is that behind the scenes the Azure SQL Database is hosted on a SQL Server, so the same concepts still apply and probably ring a bell when you hear these words: transaction log.

In the past, transaction logs were as important as the database; therefore, if there was a system failure, these logs would be necessary to bring the database to a consistent state.

Getting a transactionally consistent bacpac

First, I know that using Azure SQL we don’t see transaction logs very often, but they’re still there, trust me!

Second, and most importantly, you should never export an active database because, to have a consistent file, you must ensure that no write activity is occurring during the export. You will probably not be able to freeze write activity, your initial step is Copy the database

A copy of the database is a snapshot of the source database at the time of the copy request and transactionally consistent! So, after obtaining the copied database, it’s time to export the copied database

Hands on

As I mentioned earlier in the post, there are several ways to obtain a bacpac, and I’ll show 2 possibilities

Azure Portal

Powershell

Import-Module Az

Connect-AzAccount

# Replace by your environment information
$resourceGroupSQL = "blog-vinicius-deschamps" #
$resourceGroupStorage = "blog-vinicius-deschamps"
$sqlServer = "blogviniciusdeschamps"
$sqlUsername = "saviniciusdeschamps"
$sqlPassword = "my$up3rP@ssW0rD!"
$securePassword = ConvertTo-SecureString -String $sqlPassword -AsPlainText -Force
$sqlCredentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlUsername, $securePassword
$databaseName = "viniciusdeschamps-db"
$storageAccount = "blogviniciusdeschampsdia"
$containerName = "sqlexport"

# You don't need to change the next lines
$bacpacFilename = (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
$storageKeyType = "StorageAccessKey"
$storageKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroupStorage -AccountName $storageAccount)| Where-Object {$_.KeyName -eq "key2"}
$copyDatabaseName = (Get-Date).ToString("yyyyMMddHHmmss") + "-" + $databaseName
$bacpacFilename = (Get-Date).ToString("yyyy-MM-dd") + "-" + $copyDatabaseName + ".bacpac"
$baseStorageUri = "https://" + $storageAccount + ".blob.core.windows.net"
$bacpacUri = $baseStorageUri + "/" + $containerName + "/" + $bacpacFilename

# Copy database
Write-Host "Copying" $databaseName "to" $copyDatabaseName
New-AzSqlDatabaseCopy -ResourceGroupName $resourceGroupSQL -ServerName $sqlServer -DatabaseName $databaseName -CopyResourceGroupName $resourceGroupSQL -CopyServerName $sqlServer -CopyDatabaseName $copyDatabaseName
Write-Host "Copy completed"

# Export database
Write-Host "Exporting" $copyDatabaseName "to" $bacpacUri
$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $resourceGroupSQL -ServerName $sqlServer -DatabaseName $copyDatabaseName -StorageKeyType "StorageAccessKey" -StorageKey $storageKey.Value -StorageUri $bacpacUri -AdministratorLogin $sqlCredentials.UserName -AdministratorLoginPassword $sqlCredentials.Password
$exportRequest
$exportStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
while ($exportStatus.Status -eq "InProgress")
{
	Start-Sleep -s 10
	$exportStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
}

# Remove the copied database
Remove-AzSqlDatabase -ResourceGroupName $resourceGroupSQL -ServerName $sqlServer -DatabaseName $copyDatabaseName