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

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!

8,982 6 6 gold badges 45 45 silver badges 51 51 bronze badges asked May 9, 2016 at 12:48 673 1 1 gold badge 5 5 silver badges 5 5 bronze badges

3 Answers 3

.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

answered May 9, 2016 at 14:06 62.4k 6 6 gold badges 121 121 silver badges 239 239 bronze badges The log file is probably the biggest reason for the size difference. Commented Feb 17, 2017 at 14:26
I'd assume there's no index data in the .bacpac either, which is likely to be a BIG contributor to file size.
Commented Mar 13, 2017 at 10:42
Why do you say that is it not a substitute for a FULL backup please? And what exactly is the purpose of doing a BACPAK file please?
Commented Apr 20, 2022 at 12:04
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.