That’s because if you use the default recorder database (SQLite) this got corrupted during every backup process.
It was fixed (or at least merged) in December 2021 with https://github.com/home-assistant/core/pull/61161 / https://github.com/home-assistant/core/pull/60874.
Don’t know when and which way it was shipped (probably Supervisor? Or Home Assistant Core directly?) but if you nowadays create and restore a backup, the SQLite recorder database should work without issues.
Update:
See details @ Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser.