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?
6
8
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.
-
-
1There'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
