In azure sql database, we know the sql server agent is not available. In order to automate the exporting of the database as bacpac files to blob storage we used on depend on automation account. In this article we can able to export the bacpac file to storage account using automation account.

Important Note: This article is based on RunAs Account and AzureRM PS module, which is not supported anymore, an updated version shared by Jose Merchol Gomera Olmos here

Thank you, Jose and Alberto, for your comments and contribution.

Login to Azure Portal-->Go to automation account -->Runbooks-->browse gallery -->type backup azure sql databases to blob storage and import the module.

\n
1. Open the run book and click edit.
   
   
   \n

    

   \n
\n
2. Download the code and replace the with your details as shown below:
   

   <#\n.OUTPUTS\n\tHuman-readable informational and error messages produced during the job. Not intended to be consumed by another runbook.\n\n#>\n\n\n    $RESOURCEGROUPNAME = '<ResourceGroupName>'\n    $DatabaseServerName = '<DatabaseName>'\n    $DatabaseAdminUsername = '<AdminAccountHere>'\n    $DatabaseAdminPassword = '<YourPasswordHere>'\n    $DatabaseNames = '<DatabaseNaem>'\n    $StorageAccountName = '<StorageAccountName>'\n    $BlobStorageEndpoint = 'https://<StorageAccountName>.blob.core.windows.net/'\n    $StorageKey = '<StorageAccessKey>'\n    $BlobContainerName = '<ContainerName>'\n    $RetentionDays = '10'\n\n\n$ErrorActionPreference = 'stop'\n\nfunction Login() {\n\t$connectionName = \"AzureRunAsConnection\"\n\ttry\n\t{\n\t\t$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName         \n\n\t\tWrite-Verbose \"Logging in to Azure...\" -Verbose\n\n\t\tAdd-AzureRmAccount `\n\t\t\t-ServicePrincipal `\n\t\t\t-TenantId $servicePrincipalConnection.TenantId `\n\t\t\t-ApplicationId $servicePrincipalConnection.ApplicationId `\n\t\t\t-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint | Out-Null\n\t}\n\tcatch {\n\t\tif (!$servicePrincipalConnection)\n\t\t{\n\t\t\t$ErrorMessage = \"Connection $connectionName not found.\"\n\t\t\tthrow $ErrorMessage\n\t\t} else{\n\t\t\tWrite-Error -Message $_.Exception\n\t\t\tthrow $_.Exception\n\t\t}\n\t}\n}\n\nfunction Create-Blob-Container([string]$blobContainerName, $storageContext) {\n\tWrite-Verbose \"Checking if blob container '$blobContainerName' already exists\" -Verbose\n\tif (Get-AzureStorageContainer -ErrorAction \"Stop\" -Context $storageContext | Where-Object { $_.Name -eq $blobContainerName }) {\n\t\tWrite-Verbose \"Container '$blobContainerName' already exists\" -Verbose\n\t} else {\n\t\tNew-AzureStorageContainer -ErrorAction \"Stop\" -Name $blobContainerName -Permission Off -Context $storageContext\n\t\tWrite-Verbose \"Container '$blobContainerName' created\" -Verbose\n\t}\n}\n\nfunction Export-To-Blob-Storage([string] $RESOURCEGROUPNAME, [string]$databaseServerName, [string]$databaseAdminUsername, [string]$databaseAdminPassword, [string[]]$databaseNames, [string]$storageKey, [string]$blobStorageEndpoint, [string]$blobContainerName) {\n\tWrite-Verbose \"Starting database export to databases '$databaseNames'\" -Verbose\n\t$securePassword = ConvertTo-SecureString –String $databaseAdminPassword –AsPlainText -Force \n\t$creds = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $databaseAdminUsername, $securePassword\n\n\tforeach ($databaseName in $databaseNames.Split(\",\").Trim()) {\n\t\tWrite-Output \"Creating request to backup database '$databaseName'\"\n\n\t\t$bacpacFilename = $databaseName + (Get-Date).ToString(\"yyyyMMddHHmm\") + \".bacpac\"\n\t\t$bacpacUri = $blobStorageEndpoint + $blobContainerName + \"/\" + $bacpacFilename\n\t\t$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $RESOURCEGROUPNAME –ServerName $databaseServerName `\n\t\t\t–DatabaseName $databaseName –StorageKeytype \"StorageAccessKey\" –storageKey $storageKey -StorageUri $BacpacUri `\n\t\t\t–AdministratorLogin $creds.UserName –AdministratorLoginPassword $creds.Password -ErrorAction \"continue\"\n\t\t\n\t\t# Print status of the export\n\t\tGet-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink -ErrorAction \"Stop\"\n\t}\n}\n\nfunction Delete-Old-Backups([int]$retentionDays, [string]$blobContainerName, $storageContext) {\n\tWrite-Output \"Removing backups older than '$retentionDays' days from blob: '$blobContainerName'\"\n\t$isOldDate = [DateTime]::UtcNow.AddDays(-$retentionDays)\n\t$blobs = Get-AzureStorageBlob -Container $blobContainerName -Context $storageContext\n\tforeach ($blob in ($blobs | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq \"BlockBlob\" })) {\n\t\tWrite-Verbose (\"Removing blob: \" + $blob.Name) -Verbose\n\t\tRemove-AzureStorageBlob -Blob $blob.Name -Container $blobContainerName -Context $storageContext\n\t}\n}\n\nWrite-Verbose \"Starting database backup\" -Verbose\n\n$StorageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageKey\n\nLogin\n\nCreate-Blob-Container `\n\t-blobContainerName $blobContainerName `\n\t-storageContext $storageContext\n\t\nExport-To-Blob-Storage `\n\t-resourceGroupName $RESOURCEGROUPNAME `\n\t-databaseServerName $DatabaseServerName `\n\t-databaseAdminUsername $DatabaseAdminUsername `\n\t-databaseAdminPassword $DatabaseAdminPassword `\n\t-databaseNames $DatabaseNames `\n\t-storageKey $StorageKey `\n\t-blobStorageEndpoint $BlobStorageEndpoint `\n\t-blobContainerName $BlobContainerName\n\t\n\n\t\nWrite-Verbose \"Database backup script finished\" -Verbose\n

\n

You can get required details from azure portal as shown in below snippets:
To get the Blob container name: Go to the storage account and select the container name where you want to save the bacpac file .

To find the storage key: Go to Access keys and copy the key as shown in below image:

You can get the Blob Storage Endpoint from endpoint option as shown in the below image.

\n
1. Now go to runbook and click on edit PowerShell runbook option and replace the existing script with the modified script that you have.
   \n

    

   \n
\n
2. Now to automate click on “link to schedule” and schedule the time according to your requirement in order to run the export job and click on create.
   \n

    

   \n
\n

6.If you want to confirm If bacpac is completed successfully, you can go to job output and check the status of backup.

You can also verify the status in SQL Server import and export history wizard as shown below:

Once the backup is successful, you can find the .bacpac file in your specific storage account.

Points to ponder:

\n
1. Make sure that you have set the option Allow all azure services to “Yes”.
\n
2. This functionality works only when public end point has been used and make sure deny public access should be set to “NO\"
\n
3. Also If you encounter any issue related to module is miss then you need to go the below path to fix the issue:
   Go  the automation account-->module-->browse gallery--> Search missing module-->Import
\n

Important Note: This article is based on RunAs Account and AzureRM PS module, which is not supported anymore, an updated version shared by Jose Merchol Gomera Olmos here

Thank you, Jose and Alberto, for your comments and contribution.

Login to Azure Portal-->Go to automation account -->Runbooks-->browse gallery -->type backup azure sql databases to blob storage and import the module.

\n
1. Open the run book and click edit.
   
   
   \n

    

   \n
\n
2. Download the code and replace the with your details as shown below:
   <#\n.OUTPUTS\n\tHuman-readable informational and error messages produced during the job. Not intended to be consumed by another runbook.\n\n#>\n\n\n $RESOURCEGROUPNAME = '<ResourceGroupName>'\n $DatabaseServerName = '<DatabaseName>'\n $DatabaseAdminUsername = '<AdminAccountHere>'\n $DatabaseAdminPassword = '<YourPasswordHere>'\n $DatabaseNames = '<DatabaseNaem>'\n $StorageAccountName = '<StorageAccountName>'\n $BlobStorageEndpoint = 'https://<StorageAccountName>.blob.core.windows.net/'\n $StorageKey = '<StorageAccessKey>'\n $BlobContainerName = '<ContainerName>'\n $RetentionDays = '10'\n\n\n$ErrorActionPreference = 'stop'\n\nfunction Login() {\n\t$connectionName = \"AzureRunAsConnection\"\n\ttry\n\t{\n\t\t$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName \n\n\t\tWrite-Verbose \"Logging in to Azure...\" -Verbose\n\n\t\tAdd-AzureRmAccount `\n\t\t\t-ServicePrincipal `\n\t\t\t-TenantId $servicePrincipalConnection.TenantId `\n\t\t\t-ApplicationId $servicePrincipalConnection.ApplicationId `\n\t\t\t-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint | Out-Null\n\t}\n\tcatch {\n\t\tif (!$servicePrincipalConnection)\n\t\t{\n\t\t\t$ErrorMessage = \"Connection $connectionName not found.\"\n\t\t\tthrow $ErrorMessage\n\t\t} else{\n\t\t\tWrite-Error -Message $_.Exception\n\t\t\tthrow $_.Exception\n\t\t}\n\t}\n}\n\nfunction Create-Blob-Container([string]$blobContainerName, $storageContext) {\n\tWrite-Verbose \"Checking if blob container '$blobContainerName' already exists\" -Verbose\n\tif (Get-AzureStorageContainer -ErrorAction \"Stop\" -Context $storageContext | Where-Object { $_.Name -eq $blobContainerName }) {\n\t\tWrite-Verbose \"Container '$blobContainerName' already exists\" -Verbose\n\t} else {\n\t\tNew-AzureStorageContainer -ErrorAction \"Stop\" -Name $blobContainerName -Permission Off -Context $storageContext\n\t\tWrite-Verbose \"Container '$blobContainerName' created\" -Verbose\n\t}\n}\n\nfunction Export-To-Blob-Storage([string] $RESOURCEGROUPNAME, [string]$databaseServerName, [string]$databaseAdminUsername, [string]$databaseAdminPassword, [string[]]$databaseNames, [string]$storageKey, [string]$blobStorageEndpoint, [string]$blobContainerName) {\n\tWrite-Verbose \"Starting database export to databases '$databaseNames'\" -Verbose\n\t$securePassword = ConvertTo-SecureString –String $databaseAdminPassword –AsPlainText -Force \n\t$creds = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $databaseAdminUsername, $securePassword\n\n\tforeach ($databaseName in $databaseNames.Split(\",\").Trim()) {\n\t\tWrite-Output \"Creating request to backup database '$databaseName'\"\n\n\t\t$bacpacFilename = $databaseName + (Get-Date).ToString(\"yyyyMMddHHmm\") + \".bacpac\"\n\t\t$bacpacUri = $blobStorageEndpoint + $blobContainerName + \"/\" + $bacpacFilename\n\t\t$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $RESOURCEGROUPNAME –ServerName $databaseServerName `\n\t\t\t–DatabaseName $databaseName –StorageKeytype \"StorageAccessKey\" –storageKey $storageKey -StorageUri $BacpacUri `\n\t\t\t–AdministratorLogin $creds.UserName –AdministratorLoginPassword $creds.Password -ErrorAction \"continue\"\n\t\t\n\t\t# Print status of the export\n\t\tGet-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink -ErrorAction \"Stop\"\n\t}\n}\n\nfunction Delete-Old-Backups([int]$retentionDays, [string]$blobContainerName, $storageContext) {\n\tWrite-Output \"Removing backups older than '$retentionDays' days from blob: '$blobContainerName'\"\n\t$isOldDate = [DateTime]::UtcNow.AddDays(-$retentionDays)\n\t$blobs = Get-AzureStorageBlob -Container $blobContainerName -Context $storageContext\n\tforeach ($blob in ($blobs | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq \"BlockBlob\" })) {\n\t\tWrite-Verbose (\"Removing blob: \" + $blob.Name) -Verbose\n\t\tRemove-AzureStorageBlob -Blob $blob.Name -Container $blobContainerName -Context $storageContext\n\t}\n}\n\nWrite-Verbose \"Starting database backup\" -Verbose\n\n$StorageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageKey\n\nLogin\n\nCreate-Blob-Container `\n\t-blobContainerName $blobContainerName `\n\t-storageContext $storageContext\n\t\nExport-To-Blob-Storage `\n\t-resourceGroupName $RESOURCEGROUPNAME `\n\t-databaseServerName $DatabaseServerName `\n\t-databaseAdminUsername $DatabaseAdminUsername `\n\t-databaseAdminPassword $DatabaseAdminPassword `\n\t-databaseNames $DatabaseNames `\n\t-storageKey $StorageKey `\n\t-blobStorageEndpoint $BlobStorageEndpoint `\n\t-blobContainerName $BlobContainerName\n\t\n\n\t\nWrite-Verbose \"Database backup script finished\" -Verbose\n
\n

You can get required details from azure portal as shown in below snippets:
To get the Blob container name: Go to the storage account and select the container name where you want to save the bacpac file .

To find the storage key: Go to Access keys and copy the key as shown in below image:

You can get the Blob Storage Endpoint from endpoint option as shown in the below image.

\n
1. Now go to runbook and click on edit PowerShell runbook option and replace the existing script with the modified script that you have.
   \n

    

   \n
\n
2. Now to automate click on “link to schedule” and schedule the time according to your requirement in order to run the export job and click on create.
   \n

    

   \n
\n

6.If you want to confirm If bacpac is completed successfully, you can go to job output and check the status of backup.

You can also verify the status in SQL Server import and export history wizard as shown below:

Once the backup is successful, you can find the .bacpac file in your specific storage account.

Points to ponder:

\n
1. Make sure that you have set the option Allow all azure services to “Yes”.
\n
2. This functionality works only when public end point has been used and make sure deny public access should be set to “NO\"
\n
3. Also If you encounter any issue related to module is miss then you need to go the below path to fix the issue:
   Go  the automation account-->module-->browse gallery--> Search missing module-->Import
\n

In azure sql database, we know the sql server agent is not available. In order to automate the exporting of the database as bacpac files to blob storage we used on depend on automation account. In this article we can able to export the bacpac file to storage account using automation account.

Well done José. In the name of the Community, thank you.

Hope this works, 

I created a repo with the script changing the Run as Account login to use Manage Identity Sql Database backup

Also as AlbertoMorillo mentioned the AzureRM modules will be retired, so those were changed to Az. 

There is a migration tool as well: https://www.powershellgallery.com/packages/Az.Tools.Migration/11.0.0

This article is now obsolete. The script relies on Run As accounts and that type of connection is not supported since September 2023.

In addition, AzureRM modules will be retired on 29 February 2024.