41

I've been doing backups of my SQL Server 2014 Express databases for import to other servers and noticed a difference in file size between .bacpac and .bak.

Why is a .bacpac file so small compared to a .bak file of the same database?

Thanks for any insights!

|improve this question
44

A

  • .bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human).

    • You can rename the .bacpac to .zip to see the actual contents.

    • You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically.

    • It is a snapshot that includes User data + Schema from SQL Server or Azure SQL Database.

-

  • Backup (commonly have .bak extension) = A FULL database backup contains a complete copy of database and provides a single point-in-time to which the database can be restored. A full backup contains some transaction log records so that the restored component (database, file, or filegroup) can be made transactionally consistent.

A BACPAC is not a substitute for a FULL backup. Its just a snapshot that can be used to move a database from one server to another (or to cloud) and archiving an existing database in an open format.

From my test, below are the results

enter image description here

|improve this answer
  • 3
    So what information is the .bacpac missing that the .bak file has, that makes it smaller? – AllTradesJack Dec 2 '16 at 17:17
  • 1
    The log file is probably the biggest reason for the size difference. – Paul Spangle Feb 17 '17 at 14:26
  • 6
    I'd assume there's no index data in the .bacpac either, which is likely to be a BIG contributor to file size. – Rory Mar 13 '17 at 10:42
  • 9
    In that case, when you restore from .bacpac do you need to rebuild the indexes somehow, or is this done automatically? – jocull Mar 16 '18 at 13:27

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.