Upgrade StorageHow to merge old zotero.sqlite backup into current one?
My ~/Zotero/storage/ directory was missing some things, which I rsynced from a March 2025 backup, but then I had the "PDF articles in the Data Directory are not appearing in Library" issue: https://forums.zotero.org/discussion/comment/482290.
"Check Database Integrity" resulted in: "No errors were found in the database." This is similar to the "Library maintenance → Check library" function in Calibre https://calibre-ebook.com/; but what Zotero doesn't have is something similar to Calibre's "Library maintenance → Restore database" functionality, as Zotero doesn't store metadata backup files for each item in the directories in ~/Zotero/storage/.
To fix this, I ran opened my current zotero.sqlite in sqlitebrowser:
$ sqlitebrowser ~/Zotero/zotero.sqlite
Running this SQL query:
> SELECT * FROM items WHERE key = 'T5TTTUE7';
resulted in no results. "T5TTTUE7" is one of the items present in my March 2025 zotero.sqlite and ~/Zotero/storage/ backups, but not in my current zotero.sqlite.
Importing my backup:
> ATTACH DATABASE '/mnt/rootfs.20250302T0001/home/geremia/Zotero/zotero.sqlite' AS backup_db;
checking "T5TTTUE7" is present:
> SELECT * FROM backup_db.items WHERE key = 'T5TTTUE7';
returns 1 row.
> SELECT count(*) FROM items
returns 6438, but
> SELECT count(*) FROM backup_db.items
returns 6541, so there are items in the backup not in the current zotero.sqlite.
Doing a join shows that all items are unique:
> SELECT count(*) FROM backup_db.items as bu LEFT JOIN items on bu.key == items.key
returns 6541, the bigger size.
Now I can merge the old database into the current one:
> INSERT OR IGNORE INTO items SELECT * FROM backup_db.items;
{IGNORE will make sure newer entries are not overwritten. This assumes that identifiers like 'T5TTTUE7' are all unique, which they probably are; collisions are highly unlikely for (26 letters +10 digits)^8 ≈ 2.8×10¹² possibilities, requiring about 2×10⁶ entries before a 50% probability of collision (cf. https://mathworld.wolfram.com/BirthdayProblem.html).}
Still, this didn't fix anything, even before or after "Check[ing] Database Integrity" again.
"Check Database Integrity" resulted in: "No errors were found in the database." This is similar to the "Library maintenance → Check library" function in Calibre https://calibre-ebook.com/; but what Zotero doesn't have is something similar to Calibre's "Library maintenance → Restore database" functionality, as Zotero doesn't store metadata backup files for each item in the directories in ~/Zotero/storage/.
To fix this, I ran opened my current zotero.sqlite in sqlitebrowser:
$ sqlitebrowser ~/Zotero/zotero.sqlite
Running this SQL query:
> SELECT * FROM items WHERE key = 'T5TTTUE7';
resulted in no results. "T5TTTUE7" is one of the items present in my March 2025 zotero.sqlite and ~/Zotero/storage/ backups, but not in my current zotero.sqlite.
Importing my backup:
> ATTACH DATABASE '/mnt/rootfs.20250302T0001/home/geremia/Zotero/zotero.sqlite' AS backup_db;
checking "T5TTTUE7" is present:
> SELECT * FROM backup_db.items WHERE key = 'T5TTTUE7';
returns 1 row.
> SELECT count(*) FROM items
returns 6438, but
> SELECT count(*) FROM backup_db.items
returns 6541, so there are items in the backup not in the current zotero.sqlite.
Doing a join shows that all items are unique:
> SELECT count(*) FROM backup_db.items as bu LEFT JOIN items on bu.key == items.key
returns 6541, the bigger size.
Now I can merge the old database into the current one:
> INSERT OR IGNORE INTO items SELECT * FROM backup_db.items;
{IGNORE will make sure newer entries are not overwritten. This assumes that identifiers like 'T5TTTUE7' are all unique, which they probably are; collisions are highly unlikely for (26 letters +10 digits)^8 ≈ 2.8×10¹² possibilities, requiring about 2×10⁶ entries before a 50% probability of collision (cf. https://mathworld.wolfram.com/BirthdayProblem.html).}
Still, this didn't fix anything, even before or after "Check[ing] Database Integrity" again.
How did you end up with missing items and files? If the item was present in an old database and isn't present in your current database, you deleted the item. (And that appears to be the case for that item.)
Assuming you're using syncing, see here for how you would restore to the backup and avoid the deletions syncing down again:
https://www.zotero.org/support/zotero_data#restoring_your_zotero_data_from_a_backup_and_overwriting_synced_changes
You absolutely should not try to make direct changes to the database, and you will never receive support here again if you do. We specifically cannot support manually modified databases.
I had to "Check Database Integrity" and make Zotero automatically fix errors it found, but it worked.