Why does data compression not affect backups?

Question

The documentation is very clear on this point

Compression doesn't affect backup and restore.

but how is that possible? Surely, if my table is smaller (due to compression), then that should have some impact on backups/restores? If my table is smaller, then it should load faster. If my table needs some decompressing, then that should make it takes longer. Either way, I just can't believe that data compression does absolutely nothing to backups and restores.

What am I missing?

• Bob Klimes: The Linked Article indicates that data will be uncompressed for other SQL Server Components, so to backup the data, SQL Server will decompress it first
• Tibor Karaszi: I think that the author means that compression is transparent to backup and restore. There is no compression and decompression going on when you backup or restore.
• J. Mini: @TiborKaraszi That contradicts what the previous comment says.
• Bob Klimes: @TiborKaraszi i tend to agree with you but was just pointing out first sentence in the article implies that other part of SQL server will get the data uncompressed, maybe that extended to backups. i may have just misinterpreted the article
• Tibor Karaszi: @BobKlimes That article has IMO room for improvements regarding how it phrases that part. :-) Backup just shuffles the pages as they are, to disk (as per Charlieface's reply).

Answer 1

Backup/Restore works on whole pages of the data files.

The rows and pages are compressed and decompressed within the Storage Engine, which is the component that actually reads the column values. The Backup/Restore has no knowledge of what is actually in the pages, it just treats it as a single blob of data.

So it means that there is no compression or decompression happening when you backup or restore.

• J. Mini: So, given that my compression ought to reduce the number of pages, I should hope that data compression reduces the time taken to backup/restore?
• David Browne - Microsoft: Yes. It will reduce the IO on backup/restore.
• Tibor Karaszi: One could investigate how backup compression plays into this. Perhaps there could be some bit-patterns for which data compression causes backup compression to be less effective compared to if we didn't have data compression? I.e., we would save on reading from the database files, but lose when writing to the backup file. Probably not worth investigate except from a theoretical standpoint, though.