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

l3zydbqr  于 11个月前  发布在  其他
关注(0)|答案(4)|浏览(139)

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

7ivaypg9

7ivaypg91#

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 .

db2dz4w8

db2dz4w82#

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

llycmphe

llycmphe3#

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.

tpxzln5u

tpxzln5u4#

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"

相关问题