There are several ways to get a bacpac from an Azure SQL Database to a storage. However, you take some precautions for the export be transactionally consistent.

Transactionally Consistent

One thing not to forget is that behind the scenes the Azure SQL Database is sitting in an SQL Server, therefore the same concepts are still valid, and it probably rings the bell hearing these words: transaction log.

Back in the days, transaction logs were as important as the database, so if there is a system failure, these logs would be required to bring the database to a consistent state.

Getting a transactionally consistent bacpac

First of all, I know that using Azure SQL we don’t see the transaction logs that often but they are still there, believe in me!

Secondly, and most important 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. Most likely, you will not be able to freeze write activity, your initial step is

Copy the database

A database copy is a snapshot of the source database as of the time of the copy request, and transactionally consistent! So, once you get the copied database it is time to

Export the copied database

Hands on

As I mentioned ealier in the post, there are several ways to get a bacpac, and I am going to 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

And that’s it

I hope you liked it, and I’ll see you on my next post

Photo by chuttersnap on Unsplash