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

32
votes

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.)

4
sql-servervisual-studioazurebacpac

4 Answers

40
votes

If you have this folder on your machine C:\Program Files (x86)\Microsoft SQL Server\110\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.

14
votes

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

4
votes

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):

running <code>sqlpackage</code> from anywhere

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

  1. 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.
1
votes

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"