SQL Server 2025 Backup Compression Algorithm

Question

Post reply

SQL Server 2025 Backup Compression Algorithm

Johan Bijnens

SSC Guru

Points: 135617
More actions
December 29, 2025 at 12:00 am

#4697972

Comments posted to this topic are about the item SQL Server 2025 Backup Compression Algorithm
Johan

Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80411 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 2

Hard lessons in the past (especially for but not limited to SQL Server 2012) have "helped" me understand not to trust Microsoft for at least a year (and sometimes more) with such things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135617 More actions · Answer 3

That's why Testing is a must!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Who am I ? Sometimes this is me but most of the time this is me

Thomas Franz Hall of Fame Points: 3967 More actions · Answer 4

From the conclusion in the original article:

Keep in mind to delete all existing target backup files before you launch the next backup statement or backup job using a new compression algorithm ( or use the FORMAT backup option ).

I really wonder who is using the same backup file / media set again and again and again instead of simply creating a new set of backup files every time, either using maintenance plans or (better) Ola Hallengrens Maintenance solution.

I mean - this whole option / functionallity to put multiple backups into a single file is an ancient technology used at times, when you backed up your dbs directly onto a tape (same as this format, rewind etc. options).

Nowadays (= in the at least last 20 years) you do your backups local on the server (ideal on a separate set of disks) and use another server who then gathers / copies the backups from the SQL server either just to keep or better to keep and test it (from this server you may copy it to tapes too, if you are still using those, which is totally fine).

PS: you don't want to create your backups direct on another server, because in this case your backup fails, when there is something wrong with your network connection. Furthermore the backup time will increase, when it has to wait for the slow network.

God is real, unless declared integer.

Johan Bijnens SSC Guru Points: 135617 More actions · Answer 5

I've tested it all to see its effects on our db.

For the rare cases where we still use native backups, we use backup devices for simplicity of the commands.

Actual filenames - used by the devices - are being modified at the beginning the specific jobs.

It's just what you are used to and serves you well enough.

We are currently using Rubrik as a backup solution.

On occasion, copy_only backups are made for specific cases.