How to import .bacpac into docker Sqlserver?

Question

I installed Sqlserver on my Mac in a docker container, following the instructions from this article.

I run the container with Kitematic and managed to connect to the server using Navicat Essentials for SQl Server. The server has four databases and I can create new ones, but, ideally, I would like to import an existing database as .bacpac.

The instructions from this answer have been of use to me in the past. Can I run something similar within the container? Or, more generally, is there a way to import a database in the container?

Cocktail

Answer 1

Hi all! We finally have a preview ready for sqlpackage that is built on dotnet core and is cross-platform! Below are the links to download from. They are evergreen links, i.e. each day a new build is uploaded. This way any checked in bug fix is available the next day. Included in the .zip file is the preview EULA. linux https://go.microsoft.com/fwlink/?linkid=873926 osx https://go.microsoft.com/fwlink/?linkid=873927 windows https://go.microsoft.com/fwlink/?linkid=873928 Release notes:

The /p:CommandTimeout parameter is hardcoded to 120 Build and deployment contributors are not supported a. Need to move to .NET Core 2.1 where System.ComponentModel.Composition.dll is supported b. Need to handle case-sensitive paths SQL CLR UDT types are not supported. a. This includes SQL Server Types SqlGeography, SqlGeometry, & SqlHierarchyId Older .dacpac and .bacpac files that use Json serialization are not supported Referenced .dacpacs (e.g. master.dacpac) may not resolve due to issues with case-sensitive file systems

For lack of a better method, please provide any feedback you have here on this GitHub issue.

Thanks for giving it a try and letting us know how it goes!

https://github.com/Microsoft/mssql-docker/issues/135#issuecomment-389245587

EDIT: I've made you a Docker image for this

https://hub.docker.com/r/samuelmarks/mssql-server-fts-sqlpackage-linux/

Example of setting up a container, creating a database, copying a .bacpac file over, and importing it into aforementioned database:

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 --name sqlfts0 samuelmarks/mssql-server-fts-sqlpackage-linux
docker exec -it sqlfts0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>' -Q 'CREATE DATABASE MyDb0'
docker cp ~/Downloads/foo.bacpac sqlfts0:/opt/downloads/foo.bacpac
docker exec -it sqlfts0 dotnet /opt/sqlpackage/sqlpackage.dll /tsn:localhost /tu:SA /tp:'<YourStrong!Passw0rd>' /A:Import /tdn:MyDb0 /sf:foo.bacpac

Audra

Works like a charm, saved me a lot of troubles with this one.. I am surprised Microsoft has not documented this yet. – Bevin

Unable to find image 'mssql-server-fts-sqlpackage-linux:latest' locally docker: Error response from daemon: pull access denied for mssql-server-fts-sqlpackage-linux, repository does not exist or may require 'docker login': denied: requested access to the resource is denied. – Calculate

use samuelmarks/mssql-server-fts-sqlpackage-linux instead of mssql-server-fts-sqlpackage-linux – Population

getting an error when I try to unpack the .bacpac file: Unable to connect to master or target server 'dbname'. You must have a user with the same password in master or target server 'dbname'. – Education

<Error> <Code>PublicAccessNotPermitted</Code> <Message>Public access is not permitted on this storage account. RequestId:98754ede-b01e-0068-2c78-468040000000 Time:2021-05-11T15:17:43.0127883Z</Message> </Error> I am getting this for Windows link. It doesn't work. – Ignatz

It's 3 years old they may of changed the link or something. Maybe the official one will be helpful? - Although they only had the first changes in over a year recently github.com/microsoft/mssql-docker/commits/master. Contributions to mine are welcome. – Audra

Answer 2

It looks like Microsoft has implemented support of this on sqlpackage, with documentation!

You will have to add sqlpackage to your container.

You can download it here. (optionally, direct link to linux package here, hopefully doesn't change)

The following are instructions for running this from a windows machine -- obviously it's the bare minimum to get it working. Please change passwords, and probably put this in a docker-compose.yml for re-use.

I unzip the above package into a folder 'c:\sqlpackage' (my windows docker run doesn't allow relative paths), and then mount that into the container with the bacpac, like such:

docker run -d -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Asdf1234" -v c:\sqlpackage:/opt/sqlpackage -v c:\yourdb.bacpac:/tmp/yourdb.bacpac -p 1433:1433 --name mssql-server-example microsoft/mssql-server-linux:2017-latest

here is what a *nix user could run alternatively:

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Asdf1234' -v ./sqlpackage:/opt/sqlpackage -v ./yourdb.bacpac:/tmp/yourdb.bacpac -p 1433:1433 --name mssql-server-example microsoft/mssql-server-linux:2017-latest

and finally, attach to your container and run:

/opt/sqlpackage/sqlpackage /a:Import /tsn:. /tdn:targetdbname /tu:sa /tp:Asdf1234 /sf:/tmp/yourdb.bacpac

After this, you should be able to connect with SSMS to localhost, username and password as you provide them above, and see 'targetdbname'! These are mostly notes I wrote for myself but I'm sure others could use them too.

Starflower

It worked for me adding /TargetTrustServerCertificate:True as I was receiving the following error: The remote certificate was rejected by the provided RemoteCertificateValidationCallback – Evelynneven

Answer 3

You can use free Azure Data Studio from Microsoft. Once you have it installed, install the extension "Admin Pack for SQL Server" from Microsoft. Then you can import bacpac files with ease.

Bravo

Thanks a ton, this saved me hours of troubleshooting. I'm running mssql in docker on macos and had countless issues trying to import a .bacpac into my database using the microsoft.sqlpackage cli. – Jac

After installing the extension, I am not able to see .bacpac extension in the Restore database process. – Uranium

Answer 4

This is not a supported feature with a LINUX implementation it seems.

See this link.

Adoration