Importing Azure .bacpac to my local 2014 SQL server no-longer working

Question

I used to be able to download a SQL backup .bacpac file and load it on my local SQL server instance and call it copyX by using command prompt and typing:

cd C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin

enter, then

.\SqlPackage.exe /Action:Import /SourceFile:"C:\path\MyBackUp.bacpac" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=copyX; Integrated Security=true;"

Now I get:

* A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014. Verifying deployment plan Analyzing deployment plan Importing package schema and data into database Updating database * Error importing database:Could not import package. Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014. Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ';'. Error SQL72045: Script execution error. The executed script: CREATE MASTER KEY;

Putting up due to the many issues encountered

Answer 1

You need to:

• Install SQL server 2016 and import into that because the database scoped credential feature is only supported in SQL Server 2016 and Azure SQL databases. Otherwise you might see:

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure spX, Line 18 'TRIM' is not a recognized built-in function name.

• Update DAC\bin\SqlPackage.exe because there is a known compatibility issue otherwise you will see:

Unable to connect to master or target server 'copyX'. You must have a user with the same password in master or target server 'copyX'.

• Remove SQL credentials from .bacpac model.xml and update origin.xml hash the long way or the short way using a powershell script. Otherwise you might see:

Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ';'. Error SQL72045: Script execution error. The executed script: CREATE MASTER KEY;

Open a normal command prompt window and type:

cd C:\Program Files\Microsoft SQL Server\150\DAC\bin

Then type

.\SqlPackage.exe /Action:Import /SourceFile:"C:\path\MyBackUp-patched.bacpac" /TargetConnectionString:"Data Source=ONO2012-LAPTOP\MSSQLSERVER01;Initial Catalog=copyX; Integrated Security=true;"