I finally caved and created a FB account so I could join the RM group. Reading through past entries, I see frequent posts about Sqlite error messages (I’ve had one already) and database corruption.
What are recommended steps, if any, to keep my database in good shape to avoid this, and if one’s database gets corrupted, is all lost? Or can RM support fix it?
I am aware of the database tools in the menu. How often should those be run?
My files (other than backups) are not in the cloud.
This is the one to be alarmed by:
Database tools Rebuild Indexes and Compact may fix some of the possible causes. Drag’n’drop to a new database might succeed. GEDCOM export and import to a new database might but both DnD and GEDCOM may lose some stuff. That’s about as far as RM Support can go. Beyond that, somebody with SQLite expertise may be able to recover all or some of your data (e.g., link in that discussion).
Preventative measures include avoidance of other processes accessing the database while it’s open in RM, periodically checking your drive for degradation.
For recovery, routine backups from within RM plus other periodic backups to external drive, cloud, off-site.
Is my Time Machine hourly backup likely to cause issues? @thejerrybryan mentioned that to me previously.
No, assuming your RM databases are on your mac and not in iCloud. My mac backs up to TM ~hourly with no issues to RM files open or not.
I have only been using RM for the past 4 or 5 years. I work with it daily – also sometimes more than one database on same day(s). I have had to use tools to fix a problem (usually with index). Those are not common – but I have NEVER had a corrupted RM database (with FTM I did however). Note I am a Windows user until recently was Windows 10 and last few months Windows 11 so I can not speak if Mac experiences would be different.
My question would be if hourly backups are more likely to introduce problems than more normal and traditional methods – I am all for multiple backup methods and locations. This goes beyond my technical knowledge but if a file is being backed up while in use….seems to have increased risks - I have seen a few issues when RM reported a db file was locked even when it was open as read only by another application.
My databases are not in iCloud. However, I do store my back there.
I have little experience using software that isn’t written from the ground up for Mac, so I’m not used to these issues. It’s been 15 years since I used Windows on a regular basis.
I just don’t want to invest thousands of hours into something that’s not stable, but I suspect, much like FTM, that a lot of issues come from syncing with Ancestry, which I will not be doing.
my feeling / belief and based on the evidence of my experience the full sync creates problems not sure the root cause and a user should not be able to introduce such “issues” in most case.
that is totally understandable – like you I was frustrated with FTM. My only frustration with RM is some the enhancement have yet to be implemented – but for me that does not impact me much. As far as being stable with the database – as long as you do not other things interfering with accessing database with using RM you should not have any issues. This might include any backup activities (including cloud services) while open in RM or any sqlite tools.
Remember that the OP already reported a file locked problem with the RM database that might have been caused by Time Machine. There are no other obvious candidates for the file being locked than than Time Machine. This file lock problem apparently did not cause any corruption of the RM database.
I confess that I have never totally understood how backing up a live RM database could alone cause file corruption, no matter if it’s Dropbox or OneDrive or Time Machine or whatever. It has always seemed to me that any read operations of the database by the backup software should just be queued up and executed along with any update operations that are being issued by the SQLite app (which is RM in this case). But SQLite file update operations involve multiple writes for what the RM user sees as a single update. So I’m guessing that maybe some of the file updates succeed and then some of them encounter that the file is locked and therefore fail. Again, I don’t understand why the rest of the file update operations don’t simply wait until the file becomes unlocked. But there have been persistent reports of RM database corruption occurring in this situation.
But there are really two concerns with backing up a live SQLite database. Concern #1 is the possibility that the backup process will interfere with the SQLite app as it is trying to update the database and therefore corrupt the database immediately. That’s the one I’ve been talking about. Concern #2 is that there is no immediate corruption of the SQLite database, but that the backup files itself is corrupted because the SQLite file is being changed as it’s being read. That is a variation on the theme of you can’t wade in the same river twice because it’s a different river the second time. Well, you can’t even wade in the same river once, because the river is changing verily as you wade in it. Similarly, you really can’t backup a file that’s being changed as you back it up. This type of corruption would only matter if ever the backup file itself needed to be used to do a restore. Otherwise, the fact that the backup file itself is corrupted wouldn’t matter. It’s just that you would think you have a good backup file when you don’t.
As for me, I would never trust any RM backup file except the RMBACKUP file made by RM itself, or a copy of an RMTREE file that I made myself when RM was not running. I would never trust a copy of an RMTREE file made by Dropbox or OneDrive or Time Machine unless I knew for sure that the copy was made when RM was not running. I would prefer that Dropbox and OneDrive and TimeMachine recognize that SQLIte files were in use and not back them up while they were in use, and in turn I would prefer that Dropbox and OneDrive and TimeMachine would actually backup SQLite databases only when they are not in use. But that doesn’t seem to be the way it works.
The following is from ChatGPT. The Google AI provides essentially the same advice.
While Time Machine can back up SQLite database files, relying solely on it is not a safe or reliable method for live, actively changing databases. If Time Machine copies the database file while a write transaction is in progress, the backup will be corrupted and unusable.
The problem lies in the way Time Machine works. It performs file-level backups and cannot ensure a database is in a consistent state during the process. An active SQLite database consists of more than just the .sqlite or .db file; it also uses a journal file (or Write-Ahead Log) for atomic transactions. If Time Machine backs up these files at different instants, the backup will be inconsistent.
Safe strategies for Time Machine backups
To safely back up SQLite databases on a Mac with Time Machine, you must first create a consistent copy of the database and then allow Time Machine to back up that copy.
.backup commandThis is the most reliable method for creating a consistent backup of a live database. The .backup command in the sqlite3 command-line tool locks the database, copies its contents to a new file, and ensures the backup file is in a valid state.
Steps:
Open the Terminal app on your Mac.
Run the following command, replacing mydatabase.db with the path to your database and mydatabase_backup.db with the desired backup file path:
sqlite3 mydatabase.db ".backup 'mydatabase_backup.db'"
Store the backup file in a location that Time Machine automatically includes in its backups.
Consider writing a shell script to automate this process.
Close the database before a backup
If you can stop all applications writing to the database, you can make a safe file-level copy.
Steps:
Close any applications that might be accessing the database.
Initiate a Time Machine backup manually by clicking “Back Up Now” from the Time Machine menu in the menu bar.
Use the SQLite Backup API
For developers, the built-in SQLite Backup API provides a programmatic way to create a consistent copy of a database. It works by establishing a shared lock on the database and then copying the file. A custom script or application can use this API to create a consistent backup file for Time Machine to pick up.
Recommended workflow
For robust data integrity, a mixed approach is best:
cron or a tool like launchd) to use the sqlite3 .backup command to create a consistent copy of your database..db file to the exclusion list.Hmm… since Time Machine runs a backup every single hour, I’ll have multiple backups all the time. It seems unlikely, in the event of a database corruption, that one of those backups in a 24 hour time period would be fine. Sure, you might lose a few hours of work.
But it only risks corrupting the back up right? Not the working file? Then it seems unlikely that Time Machine could be the cause of database issues? Or can it corrupt the current open database by running in the background ?
I really don’t know. The information from the AI suggests that it’s a low risk and the AI spends way more time worrying about the integrity of the backup file than of the integrity of the database when Time Machine is involved. Still the AI suggests removing active database from backup by Time Machine, and remember the file locked problem you encountered. If I were a Mac user of RM, I would remove RMTREE files from backup by Time Machine and instead depend on RMBACKUP files for my backup. There is no problem with the RMBACKUP files being backup up by Time Machine.
That’s essentially the same concept of what I do in my Windows environment. I used to have my RMTREE files in Dropbox. I would Pause Dropbox while I was using RM and Resume Dropbox after shutting down RM. My RMTREE files are now in OneDrive. I now do the same Pause and Resume thing in OneDrive that I used to so with Dropbox. And my RMBACKUP files are still in Dropbox where they are perfectly safe. (And I back them up several other ways as well, in addition to via Dropbox.)
The accuracy of an AI is always suspect anyway (but no more than any other Web search). But the accuracy of an AI is especially supecteptible to the way a question is framed. So I re-framed my question to focus on the integrity of the active database rather than on the integrity of the backup file. Here are the results.
Yes, using Time Machine with active SQLite files can risk the integrity of the active database — not just the backup copies.
Here’s why:
How SQLite Works:SQLite databases are single-file, disk-based databases that:
The Problem with Time Machine:Time Machine makes periodic snapshots of the filesystem, which includes open files. If a snapshot or file copy operation occurs while SQLite is writing, Time Machine might read a partial or inconsistent state of the database file.
This can cause:
While rare, under certain conditions, SQLite may enter a state where:
If Time Machine tries to access an SQLite file that is currently locked for writing, it could:
SQLITE_BUSY errors in some applications
Apple’s StanceApple recommends excluding live SQLite files from Time Machine, especially if the application using the database is running. Some apps (e.g., Photos, Mail) already do this.
Best PracticesIf you’re using SQLite in an app that writes frequently, here’s what you should do:
.tmignore or tmutil addexclusion.backup API or dump files) SummarySo I asked ChatGPT “how likely is my RootsMagic 10 database to be corrupted by hourly TimeMachine backs up on my Mac?”
Here’s the response I got.
Pretty unlikely. Here’s the breakdown:
RootsMagic 10 database structure: Under the hood, RootsMagic uses an SQLite database file. SQLite is fairly resilient, but it’s not immune if a backup captures it right in the middle of a write.
macOS Time Machine behavior: Time Machine backs up changed files on an hourly basis, but it doesn’t “lock” them while it does so. If RootsMagic happens to be writing to the database at the exact moment Time Machine is copying, you could, in theory, end up with a snapshot of a half-written file. That’s where corruption risk comes in.
Practical likelihood:
On a healthy Mac with SSD and fast I/O, the window of risk is tiny, since database writes are fast.
Time Machine is smart enough to use filesystem snapshots on APFS volumes, which makes the risk even lower, because it’s grabbing a point-in-time copy rather than the live, in-progress file.
Corruption is more likely to come from crashes, forced shutdowns, or RootsMagic bugs than from Time Machine.
Best practices:
Don’t keep the RootsMagic file open for days at a time—close it when you’re done, so backups only grab stable copies.
Use RootsMagic’s own backup function periodically (those .rmgb files are compressed, stable copies). Keep those in addition to Time Machine.
If you’re really paranoid, exclude your live RootsMagic database from Time Machine and just back up the .rmgb files.
So: the odds of Time Machine corrupting your RootsMagic database are very low—close to negligible on modern macOS.
But you still want a belt-and-suspenders approach with RootsMagic’s own backups, since Time Machine snapshots aren’t a substitute for database-aware backups.
I think the risk is low enough I’ll let Time Machine continue backing up my database, as my backup file is in iCloud.
In over a decade of use, I have never had it corrupt my DevonThink files. I realize it’s always a possibility, but Time Machine has saved my bacon several times.
Of course I take ChatGPT’s advice with a grain of salt 
Oh… and my new Facebook account I made entirely to use with the RM group got banned already. Whatever. LOL
I backup regularly to Time Machine, and have always done so - I have never had a problem (touch wood!). In fact I didn’t even know about these warnings …
I hope I’m not tempting fate.
Personally I am not worried about this. I have gotten a few since RM8 came out but never any corruption.
I backup before working in RM and have only Restored when I have rarely messed up.
The FB group has over 25 thousand members and the # of people posting about this is very low, so rest easy.
You are doing great.
Thanks, I agree I think it’s a very small thing to worry about.
Time machine has never given me an issue. The only problem I have ever had was failure of a TM hard drive. TM backs up only approximately hourly and generally waits until it see enough changes to bother with. I use the “hourly” for one always connected drive and connect a 2nd SSD drive every 10 days.
My RM files do reside on my mac and not in any cloud but the entire computer is backed up nightly via iDrive. I also close all applications at night and shut down once a week usually restarting with a safe boot to clear the air.
The bigger data risk is the idiot using the computer (ie myself).