How to import a SQL BACPAC file into an Azure SQL database using Azure Bicep DeploymentScripts

Azure ARM/Bicep DeploymentScripts are a great tool to run a simple bash or PowerShell script as part of a deployment. In this case, I needed to install a SQL BACPAC file after the deployment of the Azure SQL database via the sqlpackage executable.

First, I need to specify if I want to run an AzureCLI or AzurePowerShell script, the version, retention, etc. I also need to specify if I want any environment variables populated. In this case, I want the connection information for the Azure SQL database. These get passed into the Bicep script as parameters.

param location string
param name string
param sqlAdmin string
@secure()
param sqlAdminPassword string
param databaseName string

resource sqlServer 'Microsoft.Sql/servers@2014-04-01' existing = {
  name: name
}

resource sqlDeploymentScript 'Microsoft.Resources/deploymentScripts@2020-10-01' = {
  name: '${name}-schema-deployment-script'
  location: location
  kind: 'AzureCLI'
  properties: {
    azCliVersion: '2.52.0'
    retentionInterval: 'PT1H' // Retain the script resource for 1 hour after it ends running
    timeout: 'PT5M' // Five minutes
    cleanupPreference: 'OnExpiration'
    environmentVariables: [
      {
        name: 'DBNAME'
        value: databaseName
      }
      {
        name: 'DBSERVER'
        value: sqlServer.properties.fullyQualifiedDomainName
      }
      {
        name: 'SQLCMDPASSWORD'
        secureValue: sqlAdminPassword
      }
      {
        name: 'SQLADMIN'
        value: sqlAdmin
      }
    ]

The scriptContent section is where we can provide the script we want run. When this deploys to Azure, a temporary Azure Container Instance (ACI) & Storage Account will get spun up to run our code. In my case, I want to run the sqlpackage executable, which can be used to import my BACPAC file. The easiest way to install the sqlpackage executable in my Linux ACI is via the .NET CLI.

I will install the dotnet SDK, then install the sqlpackage executable, then download my BACPAC file, then do the import.

scriptContent: '''
apk add dotnet6-sdk

dotnet tool install -g microsoft.sqlpackage

export PATH="$PATH:/root/.dotnet/tools"

wget https://github.com/.../sql.bacpac

sqlpackage /Action:Import /SourceFile:"sql.bacpac" /tsn:"tcp:$DBSERVER,1433" /tdn:"$DBNAME" /tu:"$SQLADMIN" /tp:"$SQLCMDPASSWORD"
    '''

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *