Automate Export Azure SQL DB to blob storage use Automation account
I had a support case recently question how to automate Export Azure SQL DB to a storage account (Blob container) to .bacpac files. The first attempt was made to configure a PowerShell Script to Export Azure SQL DB to blob container and to .bacpac format from here . this was successfully completed but on demand, the coming questions is. How we can run this PowerShell script into a scheduled job?, as you all know Azure SQL DB have no SQL Agent in place. One way to do this either from client side or using Azure VM to schedule run PowerShell script!
That`s fine, but what if you can do this from Azure Portal, Yes! , you can do this! using Automation account runbooks feature. With no dependency on client or Azure VM schedule task to execute PowerShell export script for you!
I`ve searched for a complete document can demonstrate how to achieve this. Unfortunately, I did not find any. So In this blog, we will walk through how to accomplish this step-by-step,
PREREQUISITES
To complete this exercise, you should have following resources in place:
1) Azure SQL Server and DB, for more reference how to create Azure SQL DB, Server from here
2) Storage account created and configured include blob container for remote backup SQL DB. For more reference how to create new storage account and associated blob container from here
STEPS
1) Go to https://portal.azure.com/
2) From More services in the bottom on the left hand search for "Automation Accounts" , select, this will open Automation account blade
3) Click Add to create a new Automation account, type in Name field: “MyAutomationAccount” or as you like to name your Automation account, select desired subscription if you register more than one subscription. (make sure all resources (SQL DB, Server, storage account) existing in the same subscription as you creating automation account). Use or create a new Resource group.
Leave Create Azure Run As account as default (Yes).
4) First, Make sure “AzureClassicRunAsConnection” and “AzureRunAsConnection” connections created automatically during Automation account creation. Note: (“AzureRunAsConnection” connection will be used in our PowerShell workflow script later to authenticate to Azure subscription)
5) Second, Verify that your Automation account has latest modules imported and are up to date (AzureRM.Automation - AzureRM.Profile - AzureRM.Sql) to Version: 2.5.0
6) If modules show a different version, i.e. 1.0.3 select modules show old version and Import to update to version 2.5.0
7) Third, Import PowerShell script Update-ModulesInAutomationToLatestVersion from runbook gallery and make sure it is published (without this PowerShell script the Export PowerShell workflow will not work)
To import Update-ModulesInAutomationToLatestVersion PowerShell script from runbook Gallery, follow below steps:
a. Go to automation account
b. Select runbooks from middle blade
c. From top bar select “Browse gallery”
d. Set the filter “Gallery Source” to “PowerShell Gallery”
e. Search for Update-ModulesInAutomationToLatestVersion PowerShell script
f. Select this script and import it
g. Revert to runbooks again
h. Select recently imported PowerShell script
i. From top bar select Edit, then Publish
j. Verify that this script in Published state
Now we will import the PowerShell workflow script!
1) From Automation account overview dashboard
2) Select runbooks
3) Click Add a runbook (make sure you select PowerShell Workflow from category not PowerShell!)
4) Select Import an existing runbook and select to add ExportAzureDB-PowerShellWorkflow (PowerShell script code below)
5) Once new runbook created, it will present in runbooks in new state!
6) Select ExportAzureDB-PowerShellWorkflow and from top bar click Edit,
7) Update required parameters match your environment and Save, then Test pane to start run this runbook to test result before set to Publish
8) Once Test pane completed, click Published, then you can assign a schedule for this runbook per your preference
9) Review storage blob container for successfully exported .bacpac file
10) You can review completed jobs from runbooks jobs
PowerShell script (copy and save as .ps1)
<#
.SYNOPSIS Export Azure SQL DB to blob storage in .bacpac format
.DESCRIPTION This runbook export Azure SQL DB to blob storage container use below parameters.
.PARAMETER DatabaseName
Name of the database
.PARAMETER ServerName
Name of the SqlServer
.PARAMETER ServerAdmin
Name of server admin login name
.PARAMETER ResourceGroupName
name of resource group contains the SqlServer and DatabaseName
.PARAMETER ServerPassword
Password for sql server admin login
.PARAMETER BaseStorageUri
full uri for storage account name include container name https://STORAGE-NAME.blob.core.windows.net/BLOB-CONTAINER-NAME/
.PARAMETER StorageKey
storage account access key "YOUR STORAGE KEY" go to storage account --> settings --> select Access Keys --> Copy/Paste key1
.NOTES
This script provided AS IS, Please review the code before executing this on production environment
For any issue or suggestion please email to: mobaioum@microsoft.com
#>
# ---- Login to Azure ----
workflow ExportAzureDB-PowerShellWorkflowScript {
inlineScript {
$connectionName = "AzureRunAsConnection"
try
{
# Get the connection "AzureRunAsConnection "
$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName
"Login to Azure"
Add-AzureRmAccount `
-ServicePrincipal `
-TenantId $servicePrincipalConnection.TenantId `
-ApplicationId $servicePrincipalConnection.ApplicationId `
-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch {
if (!$servicePrincipalConnection)
{
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
} else{
Write-Error -Message $_.Exception
throw $_.Exception
}
}
# Database to export
$DatabaseName = "hidden"
$ServerName = "hidden"
$ServerAdmin = "hidden"
$ResourceGroupName = "hidden"
$serverPassword = "hidden"
$securePassword = ConvertTo-SecureString -String $serverPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdmin, $securePassword
# Generate a unique filename for the BACPAC
$bacpacFilename = $DatabaseName + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
# Storage account info for the BACPAC
$BaseStorageUri = "https://STORAGE-NAME.blob.core.windows.net/BLOB-CONTAINER-NAME/"
$BacpacUri = $BaseStorageUri + "/Daily/" + $bacpacFilename
$StorageKeytype = "StorageAccessKey"
$StorageKey = "YOUR STORAGE KEY"
$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
-DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
-AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
# Check status of the export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
Write-Output "Azure SQL DB Export Completed at $DatabaseName"
}
}
Enjoy automated export SQL DB!