Azure SQL - import database from bacpac - temporal (system-versioned) tables from source are missing in target

Question

I need to import a DB from bacpac (created on another server on another subscription in Azure) to set up a dev test DB on my server in my subscription.

Import completed successfully, without errors in logs, but system versioned tables are not there. Literally (tested the bacpac and restored it also to local SQL Server on my machine, and they were restored, however with issues) aren't there. Running a query taken from source server returns error saying something like 'this option is for system-versioned tables but this table is not system-versioned'.

In addition to the above, there is several thousand tables which start with temp and are suffixed with hexadecimal string. Those same temp tables are present in my local SQL server (mentioned before).

I don't think this is by design.

Answer 1

Good day,

import database from bacpac

Using bacpac dows not import any database but create a new database.

It may seem like I'm petty about using the right terms but there's a big implication in understanding the meaning, since importing database suggest that you get the same database as your source (as you get when using database backup and restore), but this is far from being true when using bacpac.

In fact, this directly related to your question :-)

I need to import a DB from bacpac (created on another server on another subscription in Azure) to set up a dev test DB on my server in my subscription.

If you need to create a copy of the database and both the source and the target database are Azure SQL Database then you should use CREATE DATABASE... AS COPY OF

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-current&preserve-view=true&tabs=sqlpool#copy-a-database

Import completed successfully, without errors in logs, but system versioned tables are not there.

This can be a result of using old version of SSMS or more accurate old version of the utility SqlPackage.exe

Update the version of the SSMS and use the latest version.

Note! You might have multiple versions of SqlPackage.exe so you need to make sure you use the newer one.

Answer 2

Hi,

It's not what articles describing this functionality say.

Then you can get the help of the article... or listen to my explanation. It is up to you to choose :-)

By the way, I am not always agree with Microsoft documentation terms from time to time as well (very rare but happens) and I already edited multiple documentations when this happen. Bacpac is a great tool to export and import data and schema - in fact, this is the goal of the bacpac. Again: import/export data and schema and NOT the database

IMPORTANT! Don't confuse between import data which is done using bacpac and import database which the term that you used!

Import is import.

Import is import but bacpac is not. Again! you can choose who you want to follow.

I HIGHLY recommend you to search one of my lecture about the internals of bacpac file to learn a bit better what bacpac is

https://www.google.com/search?q=ronen+ariely+bacpac+internals&oq=ronen+ariely+bacpac+internals

You can find lectures in Hebrew and in English

I don't really care what operations are happening under the hood

You should! Bacpac is transactionally-inconsistent !

Get more info from the recording, about why you must understand what this means :-)

I don't have any other way of doing this barring playing with scripts.

I gave you another way which is the right way - using CREATE AS COPY

and we can always find more options if needed but according to the description we have this seems like best option

This doesn't work between different subscriptions, unless I don't know something that happened recently?

Well... you are wrong. PatrickC-6225 already gave you the explanation on this

Annoyingly, though, Import in SSMS onto local SQL Server from same bacpac worked better - temporal tables were restored properly, so I'm not optimistic this will be this simple.

No problem use CREATE AS COPY :-)