Restore a SQL Server bacpac file in Linux

Question

I'm working to move an Azure database to an open source DB under Linux. I've received a .bacpac file and, to start with, I thought I'd try to install MS/SQL in Linux and restore the .bacpac file so I could peruse the database. But a restore command gives me:

$ /opt/mssql/bin/sqlpackage /a:Import /tsn:tcp:localhost /tdn:dbName /tu:SA /tp:thePassword /sf:/home/user/Downloads/dbname.bacpac
An unexpected failure occurred: Data plan execution failed with message One or more errors occurred..
Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data plan execution failed with message One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.UnauthorizedAccessException: Access to the path 'C:\Users\Client\Temp\tmp4D32.tmp' is denied.
 at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
 at Microsoft.Data.Tools.Schema.Sql.Dac.Data.TemporaryStorageProvider.CreateTemporaryFile(Int64 maxSize)

Obviously the restore is assuming the existence of Windows paths. Is there a way to override this that I've missed? I'm using:

1> select @@version
2> go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64) 
 May 10 2017 12:21:23 
 Copyright (C) 2017 Microsoft Corporation. All rights reserved.
 Developer Edition (64-bit) on Linux (Linux Mint 18.1) 
(1 rows affected)

Any pointers? My MS/SQL experience dates to a time not too long after SQL server came from Sybase so right away I'm a bit behind. Thanks for any help.

Edit

I'll not answer my own question as this solution is incomplete but I did figure out how to limp along with this as much as possible. Part of what I got from the current maintainer was a schema create script. Using sqlcmd I was able to create the schema. The .bacpac file is just a zip file and, when you extract it, you have a "Data" directory with a separate directory per table. There is also a "model.xml" file that is basically the schema - this must be how the sqlpackage import command can create the database tables but I used a .sql file for mine.

For each directory there is one or more BCP files - ah finally something I remember from my Sybase days. Because I had very few tables I was able to manually use bcp to load each table in succession.

This isn't the best answer as it requires more than just the .bacpac file to work but it did work for me. If MSFT really wants people to use MS/SQL server on Linux they've got some bugs to fix.

• jimmont: I successfully created a backup, then created a new database instance from this backup using the documentation learn.microsoft.com/en-us/sql/linux/… This should provide enough detail and links to information to successfully restore from your backup or create another and walk through the process. My notes follow "§ copy database" gist.github.com/jimmont/182aad3f2caf9d17262b224f9857f4ab

Answer 1

I'd recommend trying the restore process suggested in the Microsoft Docs - https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-sqlpackage

It looks like this may help:

RESTORE DATABASE AdventureWorks
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
GO

Hope this helps with the path issue you're running into.

• stdunbar: Hi Tara - I've got a .bacpac file, not a .bak file. I see on that page that someone is having the same issue with the temporary directory that I am. I'll keep digging though.