HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlCritical

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whyfilethesamedatabasebaksmallbacpaccompared

Problem

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!

Solution

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

Context

StackExchange Database Administrators Q#137993, answer score: 62

Revisions (0)

No revisions yet.