Search code examples
sql-serverazure-sql-databaseredgatebacpac

how to check a SQL Azure bacpac is not corrupt

I just lost hours to a corrupt SQL Azure bacpac backup. None of the restore mechanisms I used reported anything wrong when restoring the backup, and schema and data seemed to be there, but given there most definitely was something wrong with that backup.

I'm using Redgate's SQL Azure backup, but afaik all that's is doing is using the create as copy of, checking until it is completed and then using the bacpac export Azure features to take the backup.

Is there a way to confirm a SQL Azure bacpac backup is not corrupt?

More information on the symptoms of the backup:

  • Doing a restore through the DAC Framework Client Side Tools or the Azure Management Portal doesn't report any error
  • Taking a quick look into the list of tables and the top 100 records of 1-2 tables looked well
  • SQL users were left into a state that couldn't be mapped to a SQL login (as if those users were created with the 'without login' option in a local database). This didn't happen in the other backups of the database.

In that question I link to someone else having a similar situation with an on premises backup that was corrupted due to running out of disk space: http://www.sqlmag.com/forums/aft/96868

Solution

  • We're going to look into more automated checking of the output of the bacpac file probably involving a temporary RESTORE - this is really the only way to check the file is complete. Before that we are looking at ensure the .bacpac file (really just a .zip file) is intact and contains the data we're expecting in there.

    If you use the other mechanism of backing up to a local SQL Server we have much more control over that.

    Feel free to drop me an e-mail if you have any more ideas or requests. richard.mitchell@red-gate.com