How to import Azure SQL backup (.bacpac) to LocalDB using Visual Studio?

Question

I imagine this would be quite a mainstream scenario but I can't find how to import an exported Azure SQL database (.bacpac) into my LocalDB in Visual Studio 2013. Am I missing something or Visual Studio missing something?

(Note: there are solutions here when one has SQL Server management studio but I don't have it and prefer not to install it, if possible.)

Favor

Answer 1

If you have one of these folders on your machine

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin
C:\Program Files\Microsoft SQL Server\160\DAC\bin

then you can run this command to restore the bacpac file:

.\SqlPackage.exe /Action:Import /SourceFile:"c:\temp\your.bacpac" /TargetConnectionString:"Data Source=(localdb)\v11.0;Initial Catalog=devdb; Integrated Security=true;"

If that folder's missing you will need to download the tooling from Microsoft.

Intramuscular

Just a quick comment, the "devbd" in the connection string specifies the database name that will be created in LocalDB. – Favor

I am not able to get past this message: *** Error importing database:Could not import package. Unable to connect to target server. – Vidicon

If you did a full installation of Visual Studio 2015 or just included this tool when installed it then you will find SqlPackage.exe in this path C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120 – Haemophilia

IMPORTANT: Make sure you have the right tooling version. I initially had only SqlPackage in \Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe which fails to install for SQL Server 2014 LocalDB. Once I installed the newer version (120) it worked : see #23376248 – Scalise

In VS2015 I find this under C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130. – Laconism

For VS2017 I was able to find it under C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140 – Dion

Stupid enough, but I get "Operation Import requires a value for the TargetDatabaseName parameter." if I specify {TargetConnectionString}, and "The TargetConnectionString argument cannot be used in conjunction with any other Target database arguments" if i also set /tdn parameter – Shaven

Answer 2

For me the right command to use was:

.\SqlPackage.exe /Action:Import /SourceFile:"c:\temp\your.bacpac" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=DBNAME;Integrated Security=true;"

Otherwise I would get an error: *** Error importing database:Could not import package. Unable to connect to master or target server 'DBNAME'. You must have a user with the same password in master or target server 'DBNAME'.

Using VS2015, in the location specified by anjdreas:

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130

Mahone

VS2017: C:\Program Files (x86)\Microsoft Visual Studio 15.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130 – Managing

It worked! For extra clarity: 1) Replace "DBNAME" in with actual name you want. 2) The folder you are looking for (...DAC\130\ in example above) should have a file 'sqlpackage.exe' in it, which is what is going to be ran. 3) To run this, open the folder with that file in Explorer, click File and Open Windows Powershell. Then paste your version of the string. The path that worked for me was: "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140". You can search for locations within VS folder looking for "sqlpackage.exe" – Hankhanke

The part after "Data Source=(localdb)\" has changed over time, and, at least right now, it's "Data Source=(localdb)\mssqllocaldb" ... Should be OK for most people, as I recall that change was around VS2017's timeframe... – Fortyish

Answer 3

If you would like to use this more than once, you can add sqlpackage to the environment variables, allowing you to run sqlpackage as a command from any folder.

See here for how to add a path to the environment variables, it's not too hard.

Then you can just run: sqlpackage from anywhere! To test out after adding, just open command prompt or powershell from any folder and type sqlpackage, and you should get something like this (remember to close all previously open prompts first so they can get the change):

Full script then:

sqlpackage /Action:Import /SourceFile:"C:...\SOMEDBBACKUP.bacpac" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=bac_give_dbimport_any_name_you_want;Integrated Security=true;"

Notes:

1. My install lists an exe with all lowercase name sqlpackage.exe, which is why I am using all lowercase sqlpackage
2. My path with VStudio 2019 at the moment is as follows, but you can play around with final path to find yours / the newest version (I could have picked from ".../DAC/130" or 140 or 150):

C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe

3. For the imported database name, it doesn't have to match what the original db name was, so "bac_give_dbimport_any_name_you_want" could be anything you want.

Hankhanke

Answer 4

When passing /TargetConnectionString parameter, I was getting the error

Operation Import requires a value for the TargetDatabaseName parameter

SqlPackage.exe seems a bit picky.

I had to provide the requested parameters - TargetDatabaseName and TargetServerName (short names tdn and tsn), and it worked.

The final command looks like this:

.\SqlPackage.exe /Action:Import /SourceFile:"c:\temp\your.bacpac" /tdn:"C:\temp\database.mdf" /tsn:"(LocalDB)\MSSQLLocalDB"

Shaven