1

It is well-known that creating a bacpac on SQL Azure does not guarantee transactional consistency when doing an export of a live, changing database.

The accepted workaround is to create a snapshot of the database first, by copying it, and then doing an export.

This approach is pretty ridiculous, because it forces users to spend extra money for relational DB storage. In fact, in the older days of SQL Azure, databases were billed by the day, so creating daily bacpacs from production databases essentially used to double the costs (it's now billed by the hour, if I'm not mistaken).

However, my question is not about this. My question is as follows - if it is acceptable for me to have a transactionally inconsistent bacpac, is there any way of actually restoring (i.e. importing it)? The problem is simple - because some constraints are no longer satisfied, the import fails (say, with a FK exception). While the bacpac restore is nothing more than re-creating the DB from the schema, followed by bulk imports, the entire process is completely opaque and not much control is given to the user. However, since Azure SQL tools are always in flux, I would not be surprised if this became possible.

So, to recap, the question: given a potentially inconsistent bacpac (i.e. some constaints won't hold), is there a way (without writing tons of code) to import it into an on-premise database?

David Airapetyan
  • 5,301
  • 4
  • 40
  • 62
  • Note: according to a recent blog post (http://azure.microsoft.com/blog/2015/04/15/azure-sql-database-built-in-backups-vs-importexport-2/), "Import/Export cannot entirely be replaced by the built-in backups. Import/Export, along with client tools like SSDT and SSMS, is still recommended for migrating data in and out of Azure SQL Database." so my problem is still relevant even with the new tiers. – David Airapetyan Jul 17 '15 at 17:14

1 Answers1

1

Try using BCP.exe to import the data.

  1. bacpac is a zip file. You can open the bacpac by changing its file extension to .zip. All data is captured in .bcp file format in ‘Data’ folder.
  2. Move Data folder out from the zip file and save it for step 4 below.
  3. Change the .zip extension back to .bacpac and import it. It creates a database with schema only.
  4. Using bcp.exe, import .bcp files to tables in the database. https://msdn.microsoft.com/en-us/library/ms162802.aspx
  5. Troubleshoot and fix the data inconsistency.

If you already know which table contains inconsistent data, you can move out bcp files for that tables only and import them using bcp.

Eric Kang
  • 491
  • 3
  • 3
  • Thanks. It would be helpful if you included a command-line example of bcp tool invocation in your answer (for step #4). Note that one would probably want to disable constraints prior to executing step 4, and follow up with "dbcc checkconstraints" at step 5. – David Airapetyan Jul 22 '15 at 05:15
  • @DavidAirapetyan [here](http://stackoverflow.com/questions/17575459/unable-to-restore-bacpac-due-to-foreign-key-conflict) is another option , hope helps. – Shaiju T Feb 20 '17 at 15:30