Determin SQL DB backup free space needed ?

Question

Hello, I have a large SQL DB currently sizes as follows:

• Data files total = 1.7 TBs
• Data used within Data files = 1.3 TBs
• Transaction log files total = 147 GBs

The local target backup drive shows 1.46 TBs available free space.

If I go to backup this DB using with Compression option will it need the full Data files total (1.7 TBs) + Transaction log files total (147 GBs) first before compressing?...or does it need the Data used within Data files (1.3 TBs) + Transaction log files total (147 GBs)?

I'm wondering how much free space the target backup drive needs and if my backup will fail or succeed.

Thanks in advance.

Answer 1

A Full backup will contain the data used within the data files (allocated extents), plus the log records for any transaction that runs during the full backup. So about 1.3 TB, which is then compressed, but the compression ratio will get is hard to predict.

Answer 2

Since I did not know the answer on the top of my head, I looked at the topic for BACKUP command in Books Online. On that page I searched for COMPRESS, and there I found a link to this page https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-ver15, which I will believe will give the answer to your question.

Answer 3

Thank you both for your informative replies, much appreciated.