Restore annotations from corrupted db

Lucas Malor · 11-23-2016, 07:01 PM

My .sqlite database gets corrupted. I backed up the old db, done a factory reset, and installed the new (cool) firmware. But now I want the annotations back. What SQL I have to perform?

DNSB · 11-23-2016, 08:48 PM

Quote:

Originally Posted by Lucas Malor

My .sqlite database gets corrupted. I had to do a factory reset, and install the new (cool) firmware. But now I want the annotations back. What SQL I have to perform?

Unless you had a backup copy of the SQLite database, you are out of luck. If you have a backup copy, you can try recovering the annotations from that. Several threads about doing so have come and gone but should still be searchable.

Lucas Malor · 11-24-2016, 04:51 AM

I have it.

johnnyb · 11-25-2016, 02:12 AM

Also: switch to a different mnufacturer of ereaders after you have secured your annotations...

Lucas Malor · 11-25-2016, 03:50 AM

Quote:

Originally Posted by johnnyb

Also: switch to a different mnufacturer of ereaders after you have secured your annotations...

Not very helpfully for the moment.

johnnyb · 11-25-2016, 03:57 AM

Quote:

Originally Posted by Lucas Malor

Not very helpfully for the moment.

Sorry. The thing is though, if you have your database, there are a number of tools that let you navigate the sql file, it’s just not very intuitive. You may check and see if http://kobonotes.com works for you. Supposed to be an online service that extracts annotations automatically (I never got it to work though)...

davidfor · 11-25-2016, 04:45 AM

@Lucas Malor: The only thing useful that johhnyb has said is the roundabout suggestion to get an SQLite management tool and have a look at the database. If you have any SQL skills, you should be able to see what you need to do. At the simplest, it is export the contents of the table Bookmarks and then import it to the new database. But, if you have purchased books or want to do some cleanup, then you need be a bit selective.

Have a look, if you have problems or want to check what you come up with, ask away.

Lucas Malor · 11-27-2016, 01:05 PM

I tried kobonotes.com, but it fails because of SQlite error. I would extract it manually, but I don't know the tables.

davidfor · 11-27-2016, 05:59 PM

Quote:

Originally Posted by Lucas Malor

I tried kobonotes.com, but it fails because of SQlite error.

kobonotes.com won't help if you want to copy the annotations to another database. It doesn't extract them in a way that is suitable. Or at least didn't the last time I looked.

Quote:

I would extract it manually, but I don't know the tables.

Did you read my last post? Both the suggestion to actually look at the database and maybe play with it, and the statement of what single table you need to look at.

Lucas Malor · 11-29-2016, 05:01 PM

I missed the table name. Thank you David, as usual. If someone is interested, this is the trick to restore the bookmarks under Linux (or Windows with minsys):

PHP Code:


			
sqlite3 BACKUP_DATABASE.sqlite

.mode insert Bookmark
.out /PATH/TO/FILE.sql 
select * from Bookmark;

CTRL+D

cat /PATH/TO/FILE.sql  | sqlite3 NEW_DATABASE.sqlite

(I suggest you to backup the db before)

11-23-2016, 07:01 PM	#1
Lucas Malor Pain in the arse Posts: 758 Karma: 77856 Join Date: Apr 2013 Device: Kobo Aura One, Kindle 4	Restore annotations from corrupted db My .sqlite database gets corrupted. I backed up the old db, done a factory reset, and installed the new (cool) firmware. But now I want the annotations back. What SQL I have to perform? Last edited by Lucas Malor; 11-24-2016 at 04:51 AM.

11-29-2016, 05:01 PM	#10
Lucas Malor Pain in the arse Posts: 758 Karma: 77856 Join Date: Apr 2013 Device: Kobo Aura One, Kindle 4	I missed the table name. Thank you David, as usual. If someone is interested, this is the trick to restore the bookmarks under Linux (or Windows with minsys): PHP Code: `sqlite3 BACKUP_DATABASE.sqlite .mode insert Bookmark .out /PATH/TO/FILE.sql select * from Bookmark; CTRL+D cat /PATH/TO/FILE.sql \| sqlite3 NEW_DATABASE.sqlite` (I suggest you to backup the db before)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Corrupted Library Won't Restore	pkw57	Library Management	6	04-02-2016 05:32 AM
Mac version: corrupted database - restore fails	neilio	Calibre	3	04-08-2013 04:28 PM
PRS-650 Restore annotations - how?	elminster	Sony Reader	13	09-01-2012 12:22 PM
DR1000 To restore the annotations of PDF?	bokjeid	iRex	2	04-20-2011 08:56 AM

11-24-2016, 04:51 AM	#3
Lucas Malor Pain in the arse Posts: 758 Karma: 77856 Join Date: Apr 2013 Device: Kobo Aura One, Kindle 4	I have it.

11-25-2016, 02:12 AM	#4
johnnyb Cloud Reader Posts: 1,113 Karma: 4000066 Join Date: Aug 2010 Device: Kindle Oasis, Kindle Scribe, iPad Pro 11	Also: switch to a different mnufacturer of ereaders after you have secured your annotations...

11-25-2016, 04:45 AM	#7
davidfor Grand Sorcerer Posts: 24,905 Karma: 47303822 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	@Lucas Malor: The only thing useful that johhnyb has said is the roundabout suggestion to get an SQLite management tool and have a look at the database. If you have any SQL skills, you should be able to see what you need to do. At the simplest, it is export the contents of the table Bookmarks and then import it to the new database. But, if you have purchased books or want to do some cleanup, then you need be a bit selective. Have a look, if you have problems or want to check what you come up with, ask away.

11-27-2016, 01:05 PM	#8
Lucas Malor Pain in the arse Posts: 758 Karma: 77856 Join Date: Apr 2013 Device: Kobo Aura One, Kindle 4	I tried kobonotes.com, but it fails because of SQlite error. I would extract it manually, but I don't know the tables.

Advert

Advert