I've a bit of a strange problem with a BACPAC I took last night using the SQL Azure Import/Export Service.
In our database there are 2 related tables.
dbo.Documents --All Documents in the database
Id
DocName
Extension
dbo.ProcessDocuments --Doc's specific to a process
Id
DocumentId (FK -> dbo.Documents.Id with Check Constraint)
ProcessId
Based on that Schema it should not be possible for the ProcessDocuments
table to include a row that does not have a companion entry in the main Documents
table.
However after I did the restore of the database in another environment I ended up with
7001 entries in ProcessDocuments
. Only 7000 equivalent entries for them in Documents
(missing 1). And the restore failed on attempting to restore the ALTER TABLE CHECK CONSTRAINT
on ProcessDocuments
The only thing I can imagine is that when the backup was being taken, it was sequentially (alphabetically???) going through the tables, and backing up the data 1 table at a time and something like the following happened.
Documents
gets backed up. Contains 7000 entriesDocuments
& Process Documents
ProcessDocuements
gets backed up. Contains 7001 entriesIf that's the case, then it creates a massive problem in terms using BACPACs as a valid disaster recovery asset, because if they're taken while the system has data in motion, it's possible that your BACPAC contains data integrity issues.
Is this the case, or can anyone shed any light on what else could have caused this ?
Data export uses bulk operations on the DB and is NOT guaranteed to be transactional, so issue like you described can and eventually will happen.
"An export operation performs an individual bulk copy of the data from each table in the database so does not guarantee the transactional consistency of the data. You can use the Windows Azure SQL Database copy database feature to make a consistent copy of a database, and perform the export from the copy." http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx
if you want to create transactionally consistent backups you have to copy the DB first (which may cost you a lot, depending on size of your db) and then export a copied DB as BACPAC (as ramiramilu pointed out) http://msdn.microsoft.com/en-us/library/windowsazure/jj650016.aspx
you can do it yourself or use RedGate SQL Azure Backup but from what I understand they follow exactly the same steps as described above, so if you choose their consistent backup option it's gonna cost you as well.