How to automate Export Azure SQL DB to blob storage use Automation account

[Mohamed_Baioumy_MSFT]

First published on MSDN on Feb 07, 2017
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 made to configure a PowerShell Script to Export Azure SQL DB to blob container and to .bacpac format from here . this trial successfully completed but on demand, the coming questions is. How can we 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; 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 create 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 (Current available Version: 3.4.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 (Current available Version: 3.4.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



Updated PowerShell script: (2nd October, 2017)
Add function to copy source DB on the same server and export copied DB to blob storage. then drop copied database.



[code language="PowerShell"]<#
.SYNOPSIS
Export Azure SQL DB to blob storage in .bacpac format
.DESCRIPTION
This PowerShell workflow runbook script copy Azure SQL DB and Export copied database to blob storage container use below parameters.

.PARAMETER ServerName
Name of the SqlServer

.PARAMETER DatabaseName
Name of the database

.PARAMETER CopyDatabaseName
Name of the Copydatabase

.PARAMETER ResourceGroupName
Name of resource group contains the SqlServer and DatabaseName

.PARAMETER $ServerAdmin
Name of the Server admin Login

.PARAMETER $serverPassword
Input Server admin password

.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-PowerShellWorkflowv2 {



param
(
# Name of the Azure SQL Database server
[parameter(Mandatory=$true)]
[string] $ServerName,

# Source Azure SQL Database name
[parameter(Mandatory=$true)]
[string] $DatabaseName,

# Target Azure SQL Database name
[parameter(Mandatory=$true)]
[string] $CopyDatabaseName,

# Resource Group Name
[parameter(Mandatory=$true)]
[string] $ResourceGroupName

)

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
}
}

# convert server admin password to secure string
$serverAdmin = "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 = "$Using:CopyDatabaseName" + (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"
New-AzureRmSqlDatabaseCopy -ResourceGroupName "$Using:ResourceGroupName" -ServerName "$Using:ServerName" -DatabaseName "$Using:DatabaseName" `
-CopyResourceGroupName "$Using:ResourceGroupName" -CopyServerName "$Using:ServerName" -CopyDatabaseName "$Using:CopyDatabaseName"

Write-Output "Azure SQL DB "$Using:CopyDatabaseName" Copy completed"

Write-Output "Azure SQL DB "$Using:CopyDatabaseName" Export Started"

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName "$Using:ResourceGroupName" -ServerName "$Using:ServerName" `
-DatabaseName "$Using:CopyDatabaseName" -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
-AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

# Check status of the export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write("Exporting")
while ($exportStatus.Status -eq "InProgress")
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
Start-Sleep -s 10
}
$exportStatus
$Status= $exportStatus.Status
if($Status -eq "Succeeded")
{
Write-Output "Azure SQL DB Export $Status for "$Using:CopyDatabaseName""
}
else
{
Write-Output "Azure SQL DB Export Failed for "$Using:CopyDatabaseName""
}


# Drop Copy Database after successful export
Remove-AzureRmSqlDatabase -ResourceGroupName "$Using:ResourceGroupName" `
-ServerName "$Using:ServerName" `
-DatabaseName "$Using:CopyDatabaseName" `
-Force

Write-Output "Azure SQL DB "$Using:CopyDatabaseName" Deleted"
}
}[/code]



Leave old PowerShell Script AS IS (in case needed):
PowerShell script (copy/past and save as .ps1)

#NOTE: 
#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 automate Export Azure SQL DB !!