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:
- 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.
- 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.
- 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.