(SQL) Tip of the Day: BACPAC and Data Inconsistency

Today’s (SQL) Tip…

Windows Azure SQL Databases do not support SQL native backup formats.  Thus, there are no “full,” “differential,” or “transaction log” backups or restores.  Instead, there are exports.  Data can be exported to Windows Azure Blob Stores, but there’s one catch.  BACPAC exports, as they are called, are not transaction-aware.  Thus, as Table 1 is exported, Table 2 may still receive changes from end-users.  When Table 2 is exported, Table 1 may have additional changes that are never stored in the export.  So, when imported, the BACPAC file may have data inconsistency and/or broken foreign key relationships.  This can be easily remedied by first creating a copy of the desired database.  Once copied, simply export the new database. 

CREATE DATABASE <new_name> AS COPY OF <original_name>

The copy command is transaction-aware and will make a perfect duplicate of the database as of a specific point in time.  As the copied database will not be known to or used by any end users, the data within will remain consistent, thus preventing any inconsistency or broken relationships.