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

Question

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!

Answer 1

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

Answer 2

A backup created via the BACKUP DATABASE command (.bak) is a page-by-page copy of a database. Note that SQL Server database contains not only data pages. There are also pages with indexes that can be large. More details here

A backup created using the Export Data-Tier Application (.bacpac) operation is simply an exported rows from a database in XML format placed in an archive. You can manually view the contents of BACPAC, just change the extension of this file to .zip.

The size of the .bacpac file is directly proportional to the number of records in a database (taking into account the compression ratio).

The size of the .bak file depends not only on the data but also on the size of the indexes.

Sometimes this can be a problem, check for example this old discussion

Therefore, the .bacpac file is usually smaller in size.

I ran the simplest tests with the AdventureWorks database and got these results.

AdventureWorks.bacpac - 17 mb
AdventureWorks.bacpac.zip - 16 mb
AdventureWorks_with_compression.bak - 49 mb
AdventureWorks_with_compression.bak.zip - 47 mb 

Should I use .bacpac to reduce my backup size?

No.

Export \ Import Data Tier Application has several significant drawbacks that are critical for large databases:

1. This is not a transactionally consistent operation. The tables are exported sequentially. There may be a problem with the foreign key. This is doubly bad because you only discover it when you try to recover. Therefore, when performing this operation, you need to take a snapshot of the database and export the snapshot.
2. It takes much longer for both export and import. At the moment when you urgently need to run a restore, you will not be able to do this because the restore from .bacpac will take several hours to complete.
3. Export can significantly load the server, especially for large tables, which, together with the previous point, makes regular backups a dubious decision.

Classic backup created using the BACKUP DATABASE command is devoid of these drawbacks, to save space use Point-in-time recovery

Export \ Import data-tier application can be considered as a way to create a backup only for small databases, in those cases when you do not have access to the file system. In this case, do not export the live database under any circumstances, be sure to create a snapshot for consistent export.

Answer 3

Well, the biggest difference is the data is compressed (using zip) for the .bacpac file which is not the case with a normal .bak file. Just compress the .bak file and see that it's a lot smaller ;)