azure-pipeline.dbbackup.yaml

resources:
  repositories:
    - repository: templates
      type: github
      name: hmcts/azure-devops-templates
      ref: master 
      endpoint: hmcts

trigger: none

parameters:
  - name: environment
    displayName: env to run against...
    type: string
    values:
    - dev
    - stg
    - test
    - ithc
    - demo
    - prod

    # The other option is you have a param for each database with a yes/no option, but that will be messy. THe object also lets us loop
  - name: databases
    displayName: list the required databases to backup
    type: object
    default:
    - vhnotification
    - vhbookings
    - vhvideo

# Use variables to peice together strings based on your params this can be done as they are set at run time
variables:
  - name: server
    value: "vh-infra-core-${{ parameters.environment }}"

  - name: sa
    value: "vhinfracore${{ parameters.environment }}"

  - name: keyvault
    value: "vh-infra-core-${{ parameters.environment }}"

jobs:
  - job: unlock_resource_lock
    ${{ if or(eq(parameters.environment, 'Stg'), eq(parameters.environment, 'Prod')) }}:
      steps:
        - template: templates/Azure/Common/unlock-lock.yaml@templates
          parameters:
            addOrDelete: delete
            azureSubscription: 'DTS-SHAREDSERVICES-${{parameters.environment}}-Video Hearings'
            lockName: ${{ parameters.environment }}-lock
            resourceGroup: vh-infra-core-${{ parameters.environment }}

  - ${{each database in parameters.databases}}:
    - job: backup_${{database}}
      steps:
        - task: AzureCLI@2
          inputs:
            azureSubscription: 'DTS-SHAREDSERVICES-${{parameters.environment}}-Video Hearings'
            scriptType: 'pscore'
            scriptLocation: 'inlineScript'
            inlineScript: |
              $username = az keyvault secret show --vault-name vh-infra-core-${{parameters.environment}} -n db-admin-username -o tsv --query "value"
              $password = az keyvault secret show --vault-name vh-infra-core-${{parameters.environment}} -n db-admin-password -o tsv --query "value"
              $key=$(az storage account keys list -n vhinfracore${{ parameters.environment }} --resource-group vh-infra-core-${{parameters.environment}}  -o tsv --query "[0].value")
              $date = get-date -format "yyyy-MM-dd_hhmmss"
              $uri = "https://vhinfracore${{parameters.environment}}.blob.core.windows.net/db-backup/${{database}}-$date.bacpac"
              az sql server firewall-rule create -g vh-infra-core-${{parameters.environment}} -s vh-infra-core-${{parameters.environment}} -n "allow-azure-services" --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
              az sql db export -p $password --admin-user $username --storage-key $key --storage-key-type StorageAccessKey --storage-uri $uri --name ${{database}} -g vh-infra-core-${{parameters.environment}} --server vh-infra-core-${{parameters.environment}}
              az sql server firewall-rule delete -g vh-infra-core-${{parameters.environment}} -s vh-infra-core-${{parameters.environment}} -n "allow-azure-services"