How can I programmatically import a .bacpac file into an on-premises SQL Server instance?

Question

I am working on a project which hosts multiple services in Microsoft Azure, including their own databases, which are Azure SQL Databases. The development happens on developer laptops using Docker Compose including the database server which is using the mcr.microsoft.com/mssql/server:2022-latest docker image.

The task at hand is to create a scripted solution with PowerShell, which can import backups into both Azure SQL Database and the SQL Server running in Docker manually. This way, we want to export and version different sets of testdata, which we want to import into Azure environments after a deployment in some cases or use it to create a data baseline for local development.

Even though both databases are Microsoft SQL Servers, they are different products built from the same codebase for different environments and different supported feature sets. (Screenshot taken in the Azure Portal)

The Azure SQL Database does not work with *.bak files, that leaves the *.bacpac format, which is the recommended solution for manual exports in Azure SQL Database (see here). The import into another Azure SQL Database is quite simple, using the New-AzSqlDatabaseImport Command. What I have been unable to find so far is a way to programmatically import the *.bacpac into my SQL Server in Docker as part of the PowerShell script.

Not all developers use Windows machines, so a solution that is cross-platform or inside the Docker container is required.

I have tried:

• using the T-SQL command RESTORE DATABASE, which didn't work, because it expects a *.bak file
• using the sqlpackage utility as described in step 6 of this article, but that utility was not found in the container (starting container process caused: exec: "/opt/mssql/bin/sqlpackage": stat /opt/mssql/bin/sqlpackage: no such file or directory: unknown)

The only way I have found so far to import the *.bacpac file so far is manually by using the SSMS Import Data-tier Application wizard, but not in any way I can use in a PowerShell script.

I really really want to stay with a backup file solution and avoid exporting my databases to T-SQL scripts. Any help is appreciated.

Answer 1

The comment above is correct - you can accomplish this by adding sqlpackage to your Docker container.

I have a Github project to convert .bak to .bacpac, and I built a Docker container with SQL Server and sqlpackage.