12

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?

0

1 Answer 1

14

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.

3
  • Perfect, exactly what I was looking for. Thank you!
    – vaindil
    Commented Feb 2, 2016 at 15:51
  • 3
    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
    Commented Jan 7, 2019 at 5:53
  • Can this be explained by saying that back-ups are like a book of data-tiers (snapshots)? I'm not still sure to understand what a back-up really is.
    – carloswm85
    Commented Feb 28, 2023 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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