Sql-server – Importing BACPAC file from azure to SQL creating an empty database

azureazure-sql-databasebacpacsql-server-2016

Exported Data-Tier_application from an Azure database to create a BACPAC of it.

SQL editions I'm trying to use to import the BACPAC:

SQL Server 2016 express.

SQL Server 2017 Developer Edition.

I droped some views and procedures to fix error and it fixed everything.

I have now a 3GB BACPAC file and when i'm trying to import it(right click – import data tier application on databases menu from SSMS ) to my SQL Server 2016 or 2017, I receive this error:enter image description here

with this error message:

TITLE: Microsoft SQL Server Management Studio

Could not import package. Warning SQL72012: The object

[DATABASE_NAME_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [DATABASE_NAME_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Error SQL72014: .Net SqlClient Data Provider: Msg
7609, Level 17, State 5, Line 2 Full-Text Search is not installed, or
a full-text component cannot be loaded. Error SQL72045: Script
execution error. The executed script: CREATE FULLTEXT INDEX ON
[dbo].[Table]
([Nome] LANGUAGE 1033, [field_1] LANGUAGE 1033)
KEY INDEX [PK_Table]
ON [table];

it's not possible to have already these DATA and LOG because It's the first time I'm restoring it.

Changing .BACPAC file to .ZIP I could see a DATA folder with a lot of BCP files:

enter image description here

The bacpac file has 3GB.

Other answers from websites just say : "wait until the end" but the export ended 4 days ago.

I have another BACPAC file from a different database and I can import it to sql server normally. I've made the same steps for both BACPAC files when exporting.

Best Answer

I ran into that problem a few minutes and found a way to remove the fulltext stuff from a bacpac.

I found this powershell script (but fixed my backup by hand, because it was faster than trying to understand and use the script)

https://gist.github.com/JohnLBevan/6a8876f71a25aa600254d7127cf1a819

  1. Rename your backup to zip
  2. extract model.xml and Origin.xml
  3. open model.xml with a text editor and look for "fulltext"
  4. Delete all fulltext elements in the xml
  5. set fulltext to false in the header
  6. Calculate the SHA256 hash of the edited xml (I used hashtab for that)
  7. Fix the hash/checksum in origin.xml
  8. Add the updated files back to the zip
  9. Rename back to bacpac and import
  10. "Worked on my machine" - I hope it helps