Backup Size

Question

Hello!

The theory:

"backupfile
Contains one row for each data or log file of a database
backup_size numeric(20,0) Size of the backup for this file in bytes."

The practice: creating a new full db backup of the Resources database leads to

1) creation of the single Resources_backup...bak file which is ~8MB in size
2) addition of the new line to the backupfile table for the Resources data file with backup_size field = ~131KB
3) addition of the new line to the backupfile table for Resources log file with backup_size field = 12KB

Q: Why is the total sum of the backup_size fields for both data and log files (~143KB) much less then the size of the backup file (~8MB)? The backup_size from backupset does display ~8MB for the .bak file.

Thank you in advance,
Michael

Answer 1

The column backedup_page_count is 16 and 16*8192 is 131072, so that size is the size of what is actually backed up. The backup file obviously needs to include meta data and also bits of the transaction log, so it will always be bigger than 131072. Although why it would be as much as 8 MB, I don't know. I took a backup on a database with more than 16 pages, but the backup was not more than 1 MB big.

Answer 2

Hi @Mikhail Firsov ,

I did a test in my environment.

In SQL server 2019, the total sum of the backup_size fields for both data and log files is also much less than the size of the backup file(.bak file).

In SQL server 2016 and SQL server 2012, the total sum of the backup_size fields for both data and log files is approximately to the size of the backup file(.bak file).

But I also did not know that why this situation is only in SQL server 2019.

You can raise a MS tick for this issue. https://support.microsoft.com/en-us/assistedsupportproducts

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Hello,

Thank you all for replies! Although the difference in size is big, I wouldn't like to spend an incident for this issue - at last we can check the real .bak size in the backupset table.

Regards,
Michael