6

I don't know if this is a bad question, but I don't understand what the difference is between normal DB backups and data-tier applications in SQL Server. I know backups can't be imported from SQL Server to an older version of SQL Server, but data-tier applications do work for this, and I've been using this to move some of our databases between servers. What are the differences from a backup perspective?

7

A backup is a full copy of all the database pages, or a dump of the database if you like, starting at page 0 and written sequentially to the backup device.

The Data-Tier application is a set of scripts that will create the database objects along with insert statements that will insert the data into the table objects afterwards.

From a backup perspective the Data-Tier application is a snapshot of the database in a certain state, you can install the application in that state but you can not restore additional data but have to copy the data from another source. The backup is a consistent state of the database and you can make log or differential backups that you can use to restore the database to a point in time.

  • Perfect, exactly what I was looking for. Thank you! – vaindil Feb 2 '16 at 15:51
  • 1
    There's one important caveat: "For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database." (azure docs). This means that if you export Data-Tier application from a a live db, you can get an inconsistent set of data (at least for Azure Sql, not sure about regular Sql Server). – qbik Jan 7 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.