Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

REST API Sample for exporting a database

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 storage account details
# you may customize this further by creating unique bacpac filenames etc.
$storageKey= "mDyvvJ...yourstoragekey...tnlXIosA=="
$storageUri="https://yourstorageaccount.blob.core.windows.net/yourcontainer/filename.bacpac"

# 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 

# 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 your AAD app:
$tenantId = "72f988bf-86f1-41af-91ab-2d7cd011db47"
# 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/Microsoft.Sql/servers/$server/databases/$database/export?api-version=2014-04-01"

# Execute the REST API command
$result = Invoke-RestMethod -Uri $restURI -Method POST -Headers $authHeader -Body $body

Write-Output $result

References

Automate Export Azure SQL DB to blob storage use Automation account

Share the post

REST API Sample for exporting a database

×

Subscribe to Msdn Blogs | Get The Latest Information, Insights, Announcements, And News From Microsoft Experts And Developers In The Msdn Blogs.

Get updates delivered right to your inbox!

Thank you for your subscription

×