Automatic SQL server 2019 Backup to Azure blob

Lucas Escudero 191 Reputation points
2023-04-24T19:13:36.44+00:00

Hello, good afternoon community. I have a server running SQL Server 2019 with 2 databases. I need through SSMS to configure a daily backup (every day at a specific time) of both databases and host them in a blob storage within Azure. I know it's in "maintenance plans" but when I run the task, it throws me an error. What could be the problem? Do I need to enter any credentials?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,623 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Jimmy Briggs 101 Reputation points
    2023-04-24T19:27:57.7633333+00:00
    # Backup an Azure SQL single database to an Azure storage container
    
    sub = "<subscription_id>"
    az account set -s $sub # or just az login
    
    # Variable block
    let "randomIdentifier=$RANDOM*$RANDOM"
    location="East US"
    resourceGroup="msdocs-azuresql-rg-$randomIdentifier"
    tag="backup-database"
    server="msdocs-azuresql-server-$randomIdentifier"
    database="msdocsazuresqldb$randomIdentifier"
    login="azureuser"
    password="Pa$$w0rD-$randomIdentifier"
    storage="msdocsazuresql$randomIdentifier"
    container="msdocs-azuresql-container-$randomIdentifier"
    bacpac="backup.bacpac"
    
    echo "Using resource group $resourceGroup with login: $login, password: $password..."
    
    echo "Creating $resourceGroup in $location..."
    az group create --name $resourceGroup --location "$location" --tags $tag
    
    echo "Creating $storage..."
    az storage account create --name $storage --resource-group $resourceGroup --location "$location" --sku Standard_LRS
    
    echo "Creating $container on $storage..."
    key=$(az storage account keys list --account-name $storage --resource-group $resourceGroup -o json --query [0].value | tr -d '"')
    az storage container create --name $container --account-key $key --account-name $storage
    
    echo "Creating $server in $location..."
    az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password
    az sql server firewall-rule create --resource-group $resourceGroup --server $server --name AllowAzureServices --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
    
    echo "Creating $database..."
    az sql db create --name $database --resource-group $resourceGroup --server $server --edition GeneralPurpose --sample-name AdventureWorksLT
    
    echo "Backing up $database..."
    az sql db export --admin-password $password --admin-user $login --storage-key $key --storage-key-type StorageAccessKey --storage-uri "https://$storage.blob.core.windows.net/$container/$bacpac" --name $database --resource-group $resourceGroup --server $server
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.