Azure PowerShell script to monitor Azure SQL DB backup status

Question

I have an azure SQL database that I want to backup using a powershell script. I have the script to backup the app and database already finish and working, but I want the script to tell me when the backup is complete. I know about the Recovery Services Vault and the wait-azurermrecoveryservicesbackupjob cmdlet but they only seem to work for azure virtual machines. Does any one have any ideas on how this can be accomplished.I just need the Powershell script to get the DB backup status. I am new to azure but have done lot of exploration and provided solution to the customers but this one is strange to me.So Could someone please help me to get out of this ? I have the script to do the DB backup but not have the script to get the success/failure status once SQL DB backup is complete in Azure.Highly appreciated your help.

I'm afraid no, there isn't any scripts can return the status that once SQL DB backup is complete. — Leon Yue
Thanks Leon.Could you please provide me the script to take azure sql DB backup through powershell script? I have the script whivh will run backup from on premise but I need the powershell script to take backup of sql DB in azure.Kindly help me in this.Much appreciated if you could help me. — PRAVEEN
Hi @PRAVEEN, please see my answer. If my answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you. — Leon Yue
Do you have any other concerns? Please feel free to let me know. — Leon Yue

Leon Yue Leon Yue · Accepted Answer · 2021-03-05T01:33:07

I'm afraid no, there isn't any scripts can return the status that once SQL DB backup is complete.

Azure SQL database only support backup to Blob Storage.

Here's the script that take Azure SQL DB backup through Powershell script:

# Sign in to Azure.
Login-AzureRmAccount

# Fill in your subscription and SQL Database details
$resourceGroup = "YourAzureSqlDatabaseResourceGroup"
$server = "YourAzureSqlServerName"
$database = "YourAzureSqlDatabaseName"
$sqlAdminLogin = "AdminUsername"
$sqlPassword = "AdminPassword"

# Custom Filename for the BACPAC
$bacpacFilename = $database + (Get-Date).ToString("yyyy-MM-dd-HH-mm") + ".bacpac"
#Storage account (container) URI
$baseStorageUri = "https://StorageAccountName.blob.core.windows.net/BlobContainerName/"
# URI for the final bacpac file
$storageUri = $baseStorageUri + $bacpacFilename
# Blob storage access key
$storageKey= "YourStorageAccessKey"


# Tenant ID from the account that created your AAD app:
$tenantId = "YourTenantID"
# This is the Application ID from your AAD app:
$applicationId = "YourApplicationID"
# This is the Secret from your AAD app:
$key = ConvertTo-SecureString "YourAzureADAppPassword" -AsPlainText -Force

# 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 $applicationId,$key
$authresult = $authContext.AcquireToken("https://management.core.windows.net/",$cred)

# Setting the request header with authentication and content type
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$authresult.CreateAuthorizationHeader()
}
# Creation of the request body with storage details and database login
$body = @{storageKeyType = 'StorageAccessKey'; `
storageKey=$storageKey; `
storageUri=$storageUri;`
administratorLogin=$sqlAdminLogin; `
administratorLoginPassword=$sqlPassword;`
authenticationType='SQL'`
} | ConvertTo-Json

# REST URI to call
$apiURI = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/servers/$server/databases/$database/export?api-version=2014-04-01"

# POST request to the REST API
$result = Invoke-RestMethod -Uri $apiURI -Method POST -Headers $authHeader -Body $body

Write-Output $result

Please ref this blogs:

HTH.

Azure PowerShell script to monitor Azure SQL DB backup status

1 Answers