MobileRead Forums - View Single Post - Aura ONE What is the best way to restore Kobo annotations from a backup?

pl8o · 07-02-2019, 09:30 PM

Progress! I seem to have mostly fixed it by blindly running SQL queries

I would still like to know what went wrong, and why, so that I can avoid it in the future. I don’t know which of the steps actually fixed it, and which ones were just me running the the maze. Any insight would be much appreciated!

Steps taken:

- Restored from second-to-last backup.
- Opened latest backup in DB Browser for SQLite and deleted everything in the Bookmark table other than the annotations added since second-to-last backup.
- Exported Bookmark table as SQL, and imported into Bookmark table from second-to-last backup.
- Made imported (non functional) annotations match old ones with:

SELECT * FROM Bookmark WHERE ContentID = ""
UPDATE Bookmark SET ContentID = "[content id from last good annotation]" WHERE ContentID = "";

and:

UPDATE Bookmark SET DateModified = "[date modified from last good annotation]" WHERE DateModified IS NULL AND VolumeID = "[book that lost its annotations]";

and:

UPDATE Bookmark SET Annotation = NULL WHERE Annotation = "" AND VolumeID = "[book that lost its annotations]";

I also deleted one fishy looking row in the bookmark table for that book that looked like it had gotten garbled.

I loaded the updated SQLite DB on.

When I clicked annotations from the home screen, they showed up, but when I clicked "Show in Book", it would show a white screen and the book would close / crash. They didn’t show up in the book when I paged through, though, and after paging through they would disappear from the annotations view.

I repeated the process, this time deleting the Digital Editions folder for the author of the book. It had the same issue with "Show in Book", at first: returning a blank screen and crashing. But when I flip through, they’re there. And now, "show in book" seems to be working too!

07-02-2019, 09:30 PM	#2
pl8o Junior Member Posts: 8 Karma: 10 Join Date: Nov 2018 Device: Kobo Sage (prev, Nook, Nook Simple Touch, Kindle 7, Kobo Touch, KA1	Progress! I seem to have mostly fixed it by blindly running SQL queries I would still like to know what went wrong, and why, so that I can avoid it in the future. I don’t know which of the steps actually fixed it, and which ones were just me running the the maze. Any insight would be much appreciated! Steps taken: - Restored from second-to-last backup. - Opened latest backup in DB Browser for SQLite and deleted everything in the Bookmark table other than the annotations added since second-to-last backup. - Exported Bookmark table as SQL, and imported into Bookmark table from second-to-last backup. - Made imported (non functional) annotations match old ones with: SELECT * FROM Bookmark WHERE ContentID = "" UPDATE Bookmark SET ContentID = "[content id from last good annotation]" WHERE ContentID = ""; and: UPDATE Bookmark SET DateModified = "[date modified from last good annotation]" WHERE DateModified IS NULL AND VolumeID = "[book that lost its annotations]"; and: UPDATE Bookmark SET Annotation = NULL WHERE Annotation = "" AND VolumeID = "[book that lost its annotations]"; I also deleted one fishy looking row in the bookmark table for that book that looked like it had gotten garbled. I loaded the updated SQLite DB on. When I clicked annotations from the home screen, they showed up, but when I clicked "Show in Book", it would show a white screen and the book would close / crash. They didn’t show up in the book when I paged through, though, and after paging through they would disappear from the annotations view. I repeated the process, this time deleting the Digital Editions folder for the author of the book. It had the same issue with "Show in Book", at first: returning a blank screen and crashing. But when I flip through, they’re there. And now, "show in book" seems to be working too!