Import a bacpac to SQL Server

This is more of a note-to-self.

I sometimes have to import a bacpac file from customer’s database (usually from Azure SQL database) to my local machine – . For most of the time it’ll be very easy when the databases are in .bak format, but for .bacpac file it can be pretty complicated.

Sqlpackage.exe is the standard tool to import the .bacpac file, and it can be found with the installation of Visual Studio (for example `C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130`) or SQL Server ( `C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin` ). Latest version should be used because they can support the older formats (.bacpac exported from older SQL Server version), but not the way around (older version might not support .bacpac files exported from newer SQL Server versions)

As a command tool, sqlpackage.exe can take a lot of parameter – and this is where the confusion starts. I tried a lot of way and this is the one works for me:

sqlpackage.exe /tcs:"Data Source=.;Initial Catalog=epicommerce;Integrated Security=True" /sf:D:\path\epicommerce.bacpac /action:import

/tcs is targetconnectionstring, while /sf is the shorthand for sourcefile.

The target database epicommerce should be created beforehand (and be empty), and SQL Server should enable the Mixed authentication mode, and your current user account is added as admin. Otherwise, change the connectionstring to something like

Data Source=.;Initial Catalog=Quicksilver.Cms;Integrated Security=False;User ID=Quicksilver;Password=Episerver15;

Using targetconnectionstring saved me a lot of headache with sqlpackage.exe.