36

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

Borek Bernard
  • 50,745
  • 59
  • 165
  • 240

4 Answers4

43

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.

anatol
  • 1,680
  • 2
  • 24
  • 47
Simon W
  • 5,481
  • 3
  • 24
  • 35
  • Just a quick comment, the "devbd" in the connection string specifies the database name that will be created in LocalDB. – Borek Bernard Jan 09 '15 at 11:18
  • 4
    I am not able to get past this message: *** Error importing database:Could not import package. Unable to connect to target server. – Rajiv Mar 12 '15 at 19:36
  • 7
    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 – Erick B Oct 16 '15 at 17:52
  • 4
    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 http://stackoverflow.com/questions/23375748/publish-dacpac-to-sql-server-2014-using-sqlpackage-exe – Simon_Weaver Mar 11 '16 at 00:21
  • 3
    In VS2015 I find this under `C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130`. – angularsen Aug 18 '16 at 11:06
  • 3
    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 – Geethanga Jan 31 '18 at 04:56
  • 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 – d.popov Feb 09 '21 at 12:57
14

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

teocomi
  • 874
  • 1
  • 11
  • 21
  • VS2017: C:\Program Files (x86)\Microsoft Visual Studio 15.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130 – Edward Olamisan Jul 29 '17 at 22:39
  • 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" – Nicholas Petersen Aug 23 '18 at 17:22
  • 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... – Auri Rahimzadeh Oct 27 '19 at 12:04
4

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.
Nicholas Petersen
  • 9,104
  • 7
  • 59
  • 69
1

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"
d.popov
  • 4,175
  • 1
  • 36
  • 47