How to use Azure DevOps to restore the SQL database from the BACPAC file in the Storage account

Introduction

In this tutorial, I'll show you how to use Terraform to create an Azure Storage account and a Key Vault, and how to store the Storage account's SAS URL in the Key Vault. We'll then show you how to use Azure DevOps to restore a SQL database from a BAC file located in the Storage account using the SAS URL we stored in the Key Vault.

Prerequisites

Before you begin, you'll need the following:

• An Azure subscription
• Terraform installed on your local machine
• An Azure DevOps account
• Basic knowledge of YAML pipelines and Azure DevOps tasks

Step 1 - Create a Terraform Configuration

First, I'll create a Terraform configuration that creates an Azure Storage account, a Key Vault, and stores the SAS URL of the storage account in the Key Vault and see, how to use Terraform to create an Azure Storage account and a Key Vault, and how to store the Storage account's SAS URL in the Key Vault

Create a new file named main.tf in a new directory on your local machine. Copy and paste the following code into the file:

# Provider configuration for AzureRM
provider "azurerm" {
  features {}
}

# Create a resource group
resource "azurerm_resource_group" "rg" {
  name     = "my-resource-group"
  location = "West US"
}

# Create an Azure Storage account
resource "azurerm_storage_account" "storage" {
  name                     = "my-storage-account"
  resource_group_name      = azurerm_resource_group.rg.name
  location                 = azurerm_resource_group.rg.location
  account_tier             = "Standard"
  account_replication_type = "GRS"
}

# Create a Key Vault
resource "azurerm_key_vault" "key_vault" {
  name                = "my-key-vault"
  resource_group_name = azurerm_resource_group.rg.name
  location            = azurerm_resource_group.rg.location

  sku_name = "standard"

  access_policy {
    tenant_id = data.azurerm_client_config.current.tenant_id
    object_id = data.azurerm_client_config.current.object_id

    key_permissions = ["get", "list"]
  }
}

# Create a storage account SAS token and store it in the Key Vault
resource "azurerm_key_vault_secret" "storage_account_sas" {
  name         = "storage-account-sas"
  value        = azurerm_storage_account.storage.primary_blob_connection_string
  key_vault_id = azurerm_key_vault.key_vault.id
}

# Output the storage account SAS URL for the SQL database restore pipeline output "storage_account_sas_url" { value = "${azurerm_storage_account.storage.primary_blob_connection_string}" }

This Terraform configuration creates a resource group, an Azure Storage account, and a Key Vault. It then creates a storage account SAS token and stores it as a secret in the Key Vault. Finally, it outputs the SAS URL of the storage account, which we'll use in the YAML pipeline to restore the SQL database.

Step 2 - Initialize and Apply the Terraform Configuration

To apply the Terraform configuration we just created, navigate to the directory containing main.tf in your terminal or command prompt and run the following commands:

 terraform init 

This command initializes the Terraform configuration.

terraform apply 

Step 3 - Restore the SQL Database from the BACPAC File in the Storage Account

Now that we've created the Azure Storage account and the Key Vault, and stored the SAS URL of the Storage account in the Key Vault, we can use Azure DevOps to restore the SQL database from the BACPAC file in the Storage account (which you upload prior to running this task).

First, we need to create a YAML pipeline in Azure DevOps that uses the SAS URL we stored in the Key Vault to restore the SQL database.

Create a new YAML pipeline in Azure DevOps by navigating to your project, clicking on Pipelines, and then clicking on New pipeline. Choose Azure Repos Git as the source and select the repository where your code is stored. Then choose Starter pipeline and click on Continue.

Replace the contents of the azure-pipelines.yml file with the following YAML pipeline:

trigger:
- main

pool:
  vmImage: 'ubuntu-latest'

steps:
- task: AzureCLI@2
  inputs:
    azureSubscription: '<your_azure_subscription>'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      # Get the SAS URL for the BACPAC file from the Key Vault
      sasUrl=$(az keyvault secret show --name storage-account-sas --vault-name <your_key_vault_name> --query value -o tsv)

      # Restore the database using the BACPAC file
      az sql db restore --dest-name <your_destination_database_name> --edition <your_destination_database_edition> 
--service-objective <your_destination_database_service_objective> --storage-uri $sasUrl --azure-resource-group <your_azure_resource_group> --no-wait

Conclusion

In this tutorial, I showed you how to use Terraform to create an Azure Storage account and a Key Vault, and how to store the SAS URL of the storage account in the Key Vault.

I then showed you how to use Azure DevOps to restore a SQL database from a BACPAC file located in the Storage account using the SAS URL we stored in the Key Vault.

By using Terraform to automate the creation of the Azure resources and Azure DevOps to automate the restore of the SQL database, we've created a repeatable and scalable process that can be used to restore SQL databases from BACPAC files located in Azure Storage accounts.