Creating an Azure SQL Database backup via Powershell

I have several Azure SQL databases (mainly Microsoft Dynamics NAV databases) on different Azure subscriptions and often I need to download a backup for some of them.

Yesterday I’ve decided to automate this process by using Powershell and the Azure REST APIs (in particular the Database – Export API). I want to have a Powershell scripts that connect to an Azure SQL Database, creates a backup (.bacpac) and download it on an Azure Storage account (Blob container).

I’ve to admit that I was thinking that this task was quite easy to do, but instead I’ve spent few hours on this.

The tricky part is that you need first to create an Azure AD application and then you need to use the context of this application to call the Azure REST API. Most Azure services (such as Azure Resource Manager providers and the classic deployment model) require your client code to authenticate with valid credentials before you can call the service’s API. Authentication is coordinated between the various actors by Azure AD, and provides your client with an access token as proof of the authentication. The token is then sent to the Azure service in the HTTP Authorization header of subsequent REST API requests.

For creating an Azure AD application from Powershell, you need to select an app name (it must be unique in your Azure AD), provide an URI (it can be a fantasy URI) and a password for creating the application.

The command to execute is the following:

$appName = "YourApp"
$uri = "http://yourapp"
$secret = ConvertTo-SecureString "YourAppPassword" -AsPlainText -Force

$azureADApplication = New-AzureRmADApplication -DisplayName $appName -HomePage $Uri -IdentifierUris $Uri -Password $secret

Then you need to create an Azure Service Principal (an identity created for use with applications) and assign the Contributor role to this service principal. The Powershell commands are as follows:

$svcprincipal = New-AzureRmADServicePrincipal -ApplicationId $azureAdApplication.ApplicationId

$roleassignment = New-AzureRmRoleAssignment -RoleDefinitionName Contributor -ServicePrincipalName $azureAdApplication.ApplicationId.Guid

If the Azure AD application is successfully created, you need to retrieve the Tenant ID and the Application ID:

Write-Output "Tenant ID:" (Get-AzureRmContext).Tenant.TenantId
Write-Output "Application ID:" $azureAdApplication.ApplicationId.Guid

You will use these values in the next steps.

To create the Azure SQL Backup process, you need to define some variables like the name of the Resource Group of your Azure SQL database, server name, database name, login and password for accessing your database:

$resourceGroup = "YourDatabaseResourceGroup"
$server = "YourServer"
$database = "YourDatabase"
$sqlAdminLogin = "AdminUsername"
$sqlPassword = "AdminPassword"
$bacpacFilename = $database + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"

From your Azure Storage account, you need to retrieve the URI and the Access Key (available from the Azure Portal by selecting your storage account and clicking on the Access Keys panel):

$baseStorageUri = "https://YourStorageAccountName.blob.core.windows.net/YourBlobContainerName/"
$storageUri = $baseStorageUri + $bacpacFilename
$storageKey= "YourStorageAccountKey"

Now you need to use the Tenant ID (here $tenantId, Application ID (here $applicationId) and secret key (here $secretkey) of your Azure AD application previously created for authenticating and acquiring an authentication token:

$authUrl = "https://login.windows.net/${tenantId}"
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]$authUrl
$cred = New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential $applicationId,$secretkey
$authresult = $authContext.AcquireToken("https://management.core.windows.net/",$cred)

Now you need to fill in the request header for the Azure API by passing the Authentication token and then send a REST request with a JSON body like described in the Database Export API reference:

$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$authresult.CreateAuthorizationHeader()
}

$body = @{storageKeyType = 'StorageAccessKey'; `
storageKey=$storageKey; `
storageUri=$storageUri;`
administratorLogin=$sqlAdminLogin; `
administratorLoginPassword=$sqlPassword;`
authenticationType='SQL'`
} | ConvertTo-Json

and then you can send the POST http request to the API endpoint:

$apiURI = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/servers/$server/databases/$database/export?api-version=2014-04-01"
$result = Invoke-RestMethod -Uri $apiURI -Method POST -Headers $authHeader -Body $body
Write-Output $result

If all is ok, the RESP API is called and the backup of your database is executed:

I you go to your Azure Blob Storage account, you can see (after few minutes, it does not appear immediately) that the .bacpac file is here ready for you:

P.S. remember to set a firewall rule in your Azure SQL database in order to be accessible from your local client IP.

In case you need them a day, the complete Powershell code of these two scripts is available here.