Time Machine created corrupted backups of SQLite databases

Targets macOS
1

Had a customer report that they can’t open databases made in my Xojo app (using the standard SQLite), when they’re restored from Time Machine.

Error 11 database corrupt.

I just checked my own time machine backups, and while the file size is identical, the record count is off and thousands of record are missing. According to Time Machine, the last backup was in June!

So what I need to know is, does anyone else have this problem and if so, were you able to solve it and finally how did you solve it?

2

That certainly could happen if the database was in multiuser mode and the backup occurred while it was open or if it’s not being closed properly.

Check out section 1.2 in this article

https://www.sqlite.org/howtocorrupt.html

1 Like
3

Thanks Greg and shee-it… The app runs 24/7, and constantly has the database open. Seems like I need to think about how to handle this and there is no way I can when Time Machine is backing up or about to backup…

Sigh…

4

Or just use the backup function periodically just in case.

1 Like
5

If SQLite has a builtin function ro make backups while the database is open:

  • create backups with that function to a different directory
  • set TimeMachine to exclude directorie(s) where database are open
  • have TimeMachine backup files in the backup directory
3 Likes