What problem are we trying to solve
Importing an existing SQL Server database into an Azure SQL Database is not a trivial task. You can only import a BACPAC file you can’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.
Architecture choices
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:
The code
The full code to build this solution is in the Using an Azure Container Instance to convert BAK to BACPAC for Import into Azure SQL Database repository.
Building the container
You will want to have Docker Desktop installed if you are running Windows or Mac. If running Windows, you’ll also need Windows Subsystem for Linux (WSL).
The Docker image is built locally first. The Dockerfile will perform the following steps:
- Pull the latest SQL Server image from the Microsoft repo.
- Install the SQL Server tools, which will be needed for sqlcmd.exe.
- Copy and unzip the sqlpackage.exe tool.
- Copy stored procedures to restore the database.
- Create a mount point folder that will point to the Azure File Share.
- Set permissions for the SQL user to run the script.
#azure sql #containers #devops #automation
