{"id":699,"date":"2020-12-09T10:00:47","date_gmt":"2020-12-09T18:00:47","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=699"},"modified":"2020-12-09T10:00:47","modified_gmt":"2020-12-09T18:00:47","slug":"using-an-azure-container-instance-to-convert-a-bak-to-bacpac-for-import-into-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/using-an-azure-container-instance-to-convert-a-bak-to-bacpac-for-import-into-azure-sql-database\/","title":{"rendered":"Using an Azure Container Instance to convert a BAK to BACPAC for Import into Azure SQL Database"},"content":{"rendered":"<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/12\/pexels-enrique-hoyos-4041587.jpg\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-704\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/12\/pexels-enrique-hoyos-4041587.jpg\" alt=\"Image pexels enrique hoyos 4041587\" width=\"1479\" height=\"1047\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/12\/pexels-enrique-hoyos-4041587.jpg 1479w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/12\/pexels-enrique-hoyos-4041587-300x212.jpg 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/12\/pexels-enrique-hoyos-4041587-1024x725.jpg 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/12\/pexels-enrique-hoyos-4041587-768x544.jpg 768w\" sizes=\"(max-width: 1479px) 100vw, 1479px\" \/><\/a>\n<div class=\"alert alert-info\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>Guest Post<\/strong><\/p>This post has been originally written by Jes Schultz (<a href=\"https:\/\/twitter.com\/grrl_geek\">@grrl_geek<\/a>), Microsoft Software Engineer, that created a very interesting solution to solve a very common challenge, and that we want to share with you all. Thanks Jes! <\/div><\/p>\n<h2>What problem are we trying to solve<\/h2>\n<p>Importing an existing SQL Server database into an Azure SQL Database is not a trivial task. You can only <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/azure-sql-bacpac-the-easy-way\/\">import a BACPAC<\/a> file you can&#8217;t attach a database or restore a backup. In some cases, you may not have direct access to the database to create a BACPAC, but you have the database .mdf or a .bak backup file available. In this solution, you will see how to take a .bak file that is in an Azure File Share, attach that File Share to an Azure Container Instance running SQL Server, restore the .bak, create the .bacpac, copy it to Azure Blob Storage, and then import it into an Azure SQL Database.<\/p>\n<h2>Architecture choices<\/h2>\n<p>This solution is based entirely on Azure PaaS services. The conversion of the file could be achieved by creating a SQL Server VM in Azure and running the processes, but the start-up time is longer and the maintenance is unwanted. The Azure resources used for this solution are:<\/p>\n<ul>\n<li><a title=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/files\/storage-files-introduction\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/files\/storage-files-introduction\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/files\/storage-files-introduction\">Storage &#8211; File Share<\/a><\/li>\n<li><a title=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/blobs\/storage-blobs-introduction\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/blobs\/storage-blobs-introduction\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/storage\/blobs\/storage-blobs-introduction\">Storage &#8211; Blob<\/a><\/li>\n<li><a title=\"https:\/\/docs.microsoft.com\/en-us\/azure\/key-vault\/general\/overview\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/key-vault\/general\/overview\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/key-vault\/general\/overview\">Key Vault<\/a><\/li>\n<li><a title=\"https:\/\/docs.microsoft.com\/en-us\/azure\/container-registry\/\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/container-registry\/\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/container-registry\/\">Container Registry<\/a><\/li>\n<li><a title=\"https:\/\/docs.microsoft.com\/en-us\/azure\/container-instances\/container-instances-overview\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/container-instances\/container-instances-overview\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/container-instances\/container-instances-overview\">Container Instances<\/a><\/li>\n<li><a title=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/sql-database-paas-overview\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/sql-database-paas-overview\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/sql-database-paas-overview\">SQL Database<\/a><\/li>\n<\/ul>\n<h2>The code<\/h2>\n<p>The full code to build this solution is in the <a title=\"https:\/\/github.com\/grrlgeek\/bak-to-bacpac\" href=\"https:\/\/github.com\/grrlgeek\/bak-to-bacpac\" data-href=\"https:\/\/github.com\/grrlgeek\/bak-to-bacpac\">Using an Azure Container Instance to convert BAK to BACPAC for Import into Azure SQL Database<\/a> repository.<\/p>\n<h3>Building the container<\/h3>\n<p>You will want to have <a title=\"https:\/\/www.docker.com\/products\/docker-desktop\" href=\"https:\/\/www.docker.com\/products\/docker-desktop\" data-href=\"https:\/\/www.docker.com\/products\/docker-desktop\">Docker Desktop<\/a> installed if you are running Windows or Mac. If running Windows, you&#8217;ll also need <a title=\"https:\/\/docs.microsoft.com\/en-us\/windows\/wsl\/install-win10\" href=\"https:\/\/docs.microsoft.com\/en-us\/windows\/wsl\/install-win10\" data-href=\"https:\/\/docs.microsoft.com\/en-us\/windows\/wsl\/install-win10\">Windows Subsystem for Linux (WSL)<\/a>.<\/p>\n<p>The Docker image is built locally first. The Dockerfile will perform the following steps:<\/p>\n<ul>\n<li>Pull the latest SQL Server image from the Microsoft repo.<\/li>\n<li>Install the SQL Server tools, which will be needed for sqlcmd.exe.<\/li>\n<li>Copy and unzip the sqlpackage.exe tool.<\/li>\n<li>Copy stored procedures to restore the database.<\/li>\n<li>Create a mount point folder that will point to the Azure File Share.<\/li>\n<li>Set permissions for the SQL user to run the script.<\/li>\n<\/ul>\n<pre class=\"prettyprint\">FROM mcr.microsoft.com\/mssql\/server\r\n\r\n# Switch to root user for access to apt-get install\r\nUSER root\r\n\r\n# Install curl\r\nRUN apt-get -y update\r\nRUN apt-get install -y curl gnupg unzip\r\n\r\n#Add the mssql-tools repository\r\nRUN curl https:\/\/packages.microsoft.com\/keys\/microsoft.asc | apt-key add -\r\nRUN curl https:\/\/packages.microsoft.com\/config\/ubuntu\/16.04\/prod.list | tee \/etc\/apt\/sources.list.d\/msprod.list\r\n\r\n#Install mssql-tools\r\nRUN apt-get -y update\r\nRUN apt-get install -y mssql-tools unixodbc-dev\r\n\r\n#Copy sqlpackage utility and other required file to root of container\r\nCOPY . \/\r\nRUN chmod 777 *.sql\r\nRUN chown -R mssql:root *.sql\r\n\r\nRUN unzip \/sqlpackage-linux-x64-en-US-15.0.4826.1.zip -d \/sqlpackage\r\nRUN chmod 777 -R \/sqlpackage\r\nRUN rm \/sqlpackage-linux-x64-en-US-15.0.4826.1.zip\r\n\r\n#Create mount point folders\r\nRUN mkdir \/mnt\/external\r\nRUN chown -R mssql:root \/mnt\r\n\r\n#Set permissions on script file\r\nRUN chmod a+x .\/create-bacpacs.sh\r\n\r\n# Switch back to mssql user and run the entrypoint script\r\nUSER mssql\r\nENTRYPOINT \/bin\/bash .\/entrypoint.sh<\/pre>\n<p>The <a title=\"http:\/\/entrypoint.sh\" href=\"http:\/\/entrypoint.sh\/\" data-href=\"http:\/\/entrypoint.sh\">entrypoint.sh<\/a> script will tell the container where to execute code from.<\/p>\n<pre class=\"prettyprint\">\/opt\/mssql\/bin\/sqlservr &amp; .\/create-bacpacs.sh<\/pre>\n<p>The <a title=\"http:\/\/create-bacpacs.sh\" href=\"http:\/\/create-bacpacs.sh\/\" data-href=\"http:\/\/create-bacpacs.sh\">create-bacpacs.sh<\/a> script does the work inside the SQL Server instance. In the script, it will:<\/p>\n<ul>\n<li>Wait 20 seconds, to give the SQL Server service time to start.<\/li>\n<li>Create the stored procedures.<\/li>\n<li>Loop through the file share to find .bak files.<\/li>\n<li>Run sqlcmd to restore the .bak and create a new database.<\/li>\n<li>Run sqlpackage to export the database as a .bacpac.<\/li>\n<\/ul>\n<pre class=\"prettyprint\">#sleep for 20 seconds and let SQL Server start all the way\r\nsleep 20\r\n\r\n\/opt\/mssql-tools\/bin\/sqlcmd -l 300 -S localhost -U sa -P $SA_PASSWORD -d master -i \"\/create_procedure_restoreheaderonly.sql\"\r\n\/opt\/mssql-tools\/bin\/sqlcmd -l 300 -S localhost -U sa -P $SA_PASSWORD -d master -i \"\/create_procedure_restoredatabase.sql\"\r\n\r\n#run the setup script to create the DB and the schema in the DB\r\n#do this in a loop because the timing for when the SQL instance is ready is indeterminate\r\nfor f in \/mnt\/external\/*.bak;\r\ndo\r\n    s=${f##*\/}\r\n    name=\"${s%.*}\"\r\n    extension=\"${s#*.}\"\r\n    echo \"Restoring $f...\"\r\n    \/opt\/mssql-tools\/bin\/sqlcmd -l 300 -S localhost -U sa -P $SA_PASSWORD -d master -q \"EXEC dbo.restoredatabase '\/mnt\/external\/$name.$extension', '$name'\"\r\n    echo \"Creating bacpac...\"\r\n    \/sqlpackage\/sqlpackage -a:\"Export\" -ssn:\"localhost\" -su:\"sa\" -sp:\"$SA_PASSWORD\" -sdn:\"$name\" -tf:\"\/mnt\/external\/$name.bacpac\"\r\ndone<\/pre>\n<p>The next step is to create the Docker image locally. Make sure you are in the Docker directory to run this script.<\/p>\n<pre class=\"prettyprint\"># Create Docker image locally from Dockerfile\r\n\r\ndocker build -t mssql-bak-bacpac .<\/pre>\n<p>The last step to test locally is to create a container based on the image. Create a directory, C:\/Docker\/bak. Put a sample .bak in the directory. The SQL Server sa password must be entered here. Make sure this password is secured in a secret management store (such as Azure Key Vault). You will need to use this sa password when you create containers based on the image in Azure later.<\/p>\n<pre class=\"prettyprint\"># An SA_PASSWORD must be enetered here \r\ndocker run -e \"ACCEPT_EULA=Y\" -e \"SA_PASSWORD=\" `\r\n-v C:\/Docker\/bak:\/mnt\/external `\r\n--name bak-to-bacpac mssql-bak-bacpac<\/pre>\n<h3>Deploying Azure resources<\/h3>\n<p>The next step is to set up the Azure infrastructure. You will need the following resources. The Github repo contains scripts to create these.<\/p>\n<ul>\n<li>Resource Group<\/li>\n<li>Key Vault<\/li>\n<li>Storage function<\/li>\n<li>Storage File Share and Blob Container<\/li>\n<li>Container Registry<\/li>\n<li>SQL server<\/li>\n<\/ul>\n<h3>Creating the container in Azure<\/h3>\n<p>With the infrastructure created, the local image can now be pushed to the Container Registry.<\/p>\n<pre class=\"prettyprint\">$RGName = 'sqlcontainers' \r\n$ACRName = 'acrsqlcontainers'\r\n$ACRPath = 'sql\/bak-bacpac:latest'\r\n\r\n# Log in to registry \r\n$ACRNameObj = Get-AzContainerRegistry -ResourceGroupName $RGName -Name $ACRName\r\n$ACRCred = Get-AzContainerRegistryCredential -Registry $ACRNameObj\r\n\r\n# Call docker login, passing in password \r\n$ACRCred.Password | docker login $ACRNameObj.LoginServer --username $ACRCred.Username --password-stdin\r\n\r\n# Tag image \r\n$ImagePath = $ACRNameObj.LoginServer + '\/' + $ACRPath\r\ndocker tag mssql-bak-bacpac $ImagePath\r\n\r\n# Push image to repository \r\ndocker push $ImagePath<\/pre>\n<p>Now the image that is stored in the Container Registry can be used to create a new Container Instance. This cmdlet, when run, will create the container and execute the script inside it.<\/p>\n<p>This is where you will need to enter the sa password you used in your Docker image.<\/p>\n<pre class=\"prettyprint\">$SA_PASSWORD = Read-Host -Prompt \"Please enter the SA password:\"\r\n$RGName = 'sqlcontainers'\r\n$KVName = 'kvsqlcontainers'\r\n$ContainerGroupName = 'aci-sql-bak-bacpac'\r\n$ACRName = 'acrsqlcontainers'\r\n$ACRLoginServer = (Get-AzContainerRegistry -ResourceGroupName $RGName -Name $ACRName).LoginServer\r\n$ACRUser = (Get-AzKeyVaultSecret -VaultName $KVName  -Name 'acr-pull-user').SecretValueText\r\n$ACRPass = (Get-AzKeyVaultSecret -VaultName $KVName -Name 'acr-pull-pass').SecretValue\r\n$ACRCred = New-Object System.Management.Automation.PSCredential ($ACRUser, $ACRPass)\r\n$ACRPath = 'sql\/bak-bacpac:latest'\r\n$EnvVariables = @{ ACCEPT_EULA=\"Y\"; SA_PASSWORD=$SA_PASSWORD; MSSQL_PID=\"Enterprise\";}\r\n$StorageAcctName = 'customersqlbaks'\r\n$StorageAcctKey = (Get-AzStorageAccountKey -ResourceGroupName $RGName -Name $StorageAcctName)[0].Value | ConvertTo-SecureString -AsPlainText -Force\r\n$StorageAcctCred = New-Object System.Management.Automation.PSCredential($StorageAcctName, $StorageAcctKey)\r\n$StorageAcctFileShareName = 'baks'\r\n$VolumeMountPath  = '\/mnt\/external'\r\n\r\n$CGExists = Get-AzContainerGroup -ResourceGroupName $RGName -Name $ContainerGroupName -ErrorAction SilentlyContinue\r\nif ($CGExists -eq $null)\r\n    {\r\n        New-AzContainerGroup `\r\n            -Name $ContainerGroupName `\r\n            -ResourceGroupName $RGName `\r\n            -Image $ACRLoginServer\/$ACRPath  `\r\n            -RegistryServerDomain $ACRLoginServer `\r\n            -RegistryCredential $ACRCred `\r\n            -DnsNameLabel $ContainerGroupName `\r\n            -IpAddressType Public `\r\n            -EnvironmentVariable $EnvVariables `\r\n            -AzureFileVolumeAccountCredential $StorageAcctCred `\r\n            -AzureFileVolumeShareName $StorageAcctFileShareName `\r\n            -AzureFileVolumeMountPath $VolumeMountPath `\r\n            -OsType Linux `\r\n            -Cpu 2 `\r\n            -MemoryInGB 4 \r\n\r\n        Write-Host \"Container group ($ContainerGroupName) created.\"\r\n    }\r\nelse \r\n    {\r\n        Write-Host \"Container group ($ContainerGroupName) exists.\"\r\n    }<\/pre>\n<p>You can verify that your Azure File Share now contains a .bak and a .bacpac.<\/p>\n<h3>Importing the database<\/h3>\n<p>Once the .bacpac is in your file share, all that remains is to copy the file to the blob container, and import the database.<\/p>\n<pre class=\"prettyprint\">$RGName = 'sqlcontainers'\r\n$KVName = 'kvsqlcontainers'\r\n$StorageAcctName = 'customersqlbaks'\r\n$StorageAcctKey = (Get-AzStorageAccountKey -ResourceGroupName $RGName -Name $StorageAcctName)[0].Value \r\n$StorageAcctFileShareName = 'baks'\r\n$StorageContext = (Get-AzStorageAccount -ResourceGroupName $RGName -Name $StorageAcctName).Context\r\n$StorageFileShareObj = Get-AzStorageFile -ShareName $StorageAcctFileShareName -Context $StorageContext\r\n$Filtered = $StorageFileShareObj | Where-Object {$_.name -like '*.bacpac'}\r\n$FileName = $Filtered.Name\r\n$SASToken = New-AzStorageAccountSASToken -Service Blob,File,Table,Queue -ResourceType Service,Container,Object -Permission \"racwdlup\" -Context $StorageContext\r\n$StorageUriFileShareSAS = \"https:\/\/$StorageAcctName.file.core.windows.net\/$StorageAcctFileShareName\/$FileName$SASToken\"\r\n$StorageUriBlob = \"https:\/\/$StorageAcctName.blob.core.windows.net\/$StorageAcctFileShareName\/$FileName\"\r\n$StorageUriBlobSAS = \"https:\/\/$StorageAcctName.blob.core.windows.net\/$StorageAcctFileShareName\/$FileName$SASToken\"\r\n$SqlServerName = 'customerdbsfrombak'\r\n$SqlAdminUser = (Get-AzSqlServer -ResourceGroup $RGName -Name $SqlServerName).SqlAdministratorLogin\r\n$SqlAdminPass =  (Get-AzKeyVaultSecret -VaultName $KVName -Name \"$SqlServerName-admin\").SecretValue \r\n$SQLDB = 'importedbak'\r\n$sqlEdition = 'BusinessCritical'\r\n$sqlSLO = 'BC_Gen5_2'\r\n\r\n#Move file using azcopy \r\nazcopy login\r\nazcopy copy $StorageUriFileShareSAS $StorageUriBlobSAS\r\n\r\nWrite-Output \"Importing bacpac...\"\r\n$importRequest = New-AzSqlDatabaseImport `\r\n    -DatabaseName $SQLDB `\r\n    -Edition $sqlEdition `\r\n    -ServiceObjectiveName $sqlSLO `\r\n    -DatabaseMaxSizeBytes \"$(10 * 1024 * 1024 * 1024)\" `\r\n    -ServerName $SqlServerName `\r\n    -StorageKeyType 'StorageAccessKey' `\r\n    -StorageKey $StorageAcctKey `\r\n    -StorageUri $StorageUriBlob `\r\n    -AdministratorLogin $SqlAdminUser `\r\n    -AdministratorLoginPassword $SqlAdminPass `\r\n    -ResourceGroupName $RGName \r\ndo {\r\n    $importStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink\r\n    Start-Sleep -s 10\r\n} while ($importStatus.Status -eq \"InProgress\")<\/pre>\n<p>You can now go to your Azure SQL blade in the portal and see your database.<\/p>\n<p>This solution helps solve the problem of not being able to restore a .bak file into an Azure SQL Database, but without the overhead of creating and maintaining a virtual machine. The code can be downloaded from <a title=\"https:\/\/github.com\/grrlgeek\/bak-to-bacpac\" href=\"https:\/\/github.com\/grrlgeek\/bak-to-bacpac\" data-href=\"https:\/\/github.com\/grrlgeek\/bak-to-bacpac\">Using an Azure Container Instance to convert BAK to BACPAC for Import into Azure SQL Database<\/a>.<\/p>\n<hr \/>\n<p>Photo by\u00a0<a href=\"https:\/\/www.pexels.com\/@enriquehoyos?utm_content=attributionCopyText&amp;utm_medium=referral&amp;utm_source=pexels\">Enrique Hoyos<\/a>\u00a0from\u00a0<a href=\"https:\/\/www.pexels.com\/photo\/ship-in-calm-sea-near-port-4041587\/?utm_content=attributionCopyText&amp;utm_medium=referral&amp;utm_source=pexels\">Pexels<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Importing a  SQL Server backup into an Azure SQL Database is not a trivial task. Here&#8217;s how to automate the process of converting a .bak to .bacpac file using Docker Containers, Powershell and some automation.<\/p>\n","protected":false},"author":24720,"featured_media":704,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,484,92],"tags":[443,437,439,438,436,440],"class_list":["post-699","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-containers","category-devops","tag-automation","tag-backup","tag-export","tag-import","tag-restore","tag-script"],"acf":[],"blog_post_summary":"<p>Importing a  SQL Server backup into an Azure SQL Database is not a trivial task. Here&#8217;s how to automate the process of converting a .bak to .bacpac file using Docker Containers, Powershell and some automation.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/699","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/24720"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=699"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/699\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/704"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}