First published on MSDN on Sep 05, 2017
While working on an Azure SQL Database support case, I needed to provide a REST API sample for exporting a database. I struggled a bit to get it to work, as the syntax is not really intuitive and requires creating an Azure AD application.
To save you from the same struggle, here is the sample code that finally worked. You can use the same approach for other Azure SQL Database-related tasks, e.g. for configuring Auditing.
Before running the PowerShell steps below, you need to create an Azure AD application first. Please see the related article
How to create an Azure AD application in PowerShell
to fill in the parameter values for Tenant ID, Client ID, and Key.
PowerShell Code Sample
# Sign in to Azure.
Login-AzureRmAccount
# If your Azure account is on a non-public cloud, make sure to specify the proper environment
# example for the German cloud:
# Login-AzureRmAccount -EnvironmentName AzureGermanCloud
# Fill in your subscription and SQL Database details
$subscriptionId = "11111111-aaaa-bbbb-cccc-222222222222"
$resourceGroup = "yourresourcegroup"
$server = "yourserver"
$database = "yourdatabase"
$sqlAdminLogin = "sqladmin"
# $sqlPassword = "yourpassword"
# may break if your password contains characters used by PowerShell, e.g. the $ sign
# instead setting it directly in request body further below
# Generate a unique filename for the BACPAC
$bacpacFilename = $database + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
# Fill in your storage account and container name
$baseStorageUri = "
https://yourstorageaccount.blob.core.windows.net/yourcontainer/"
# Compose the storage URI
$storageUri = $baseStorageUri + $bacpacFilename
# Fill in your storage access key
$storageKey= "mDyvvJ...yourstoragekey...tnlXIosA=="
# If you have multiple subscriptions, uncomment and set to the subscription you want to work with:
# Set-AzureRmContext -SubscriptionId $subscriptionId
# This is the Tenant ID from the account that created your AAD app:
$tenantId = "99999999-zzzz-yyyy-xxxx-888888888888"
# This is the Application ID from your AAD app:
$clientId = "54c45a1a-5c1a-40ad-88b6-a37e82223eda"
# This is the Secret from your AAD app:
$key = "yoursecret"
# Acquire the authentication context
$authUrl = "
https://login.windows.net/${tenantId}"
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]$authUrl
$cred = New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential $clientId,$key
$authresult = $authContext.AcquireToken("
https://management.core.windows.net/",$cred)
# Fill in the request header with authentication and content type
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$authresult.CreateAuthorizationHeader()
}
# Fill in the request body with storage details and database login
$body = @{storageKeyType = 'StorageAccessKey'; `
storageKey=$storageKey; `
storageUri=$storageUri;`
administratorLogin=$sqlAdminLogin; `
administratorLoginPassword='yourpassword';`
authenticationType='SQL'`
} | ConvertTo-Json
# Compile the details for the REST URI
$restURI = "
https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/M..."
# Execute the REST API command
$result = Invoke-RestMethod -Uri $restURI -Method POST -Headers $authHeader -Body $body
Write-Output $result
References
https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/02/07/automate-export-azure-sql-db-to-blob-s...
