CODEWITHSUNDEEP
sqlite
GoldenJoe
GoldenJoe

Reputation: 8002

How to recover data from a corrupt SQLite3 database?

I've recovered data from a formatted hard drive for use in a lawsuit. The data is Skype logs, which are stored in SQLite3 databases. Unfortunately, the disk was formatted and a new copy of OS X was installed on the drive. I scanned the drive and found the files I am looking for, but it seems that the database I'm after is corrupt.

I tried the following command I found by searching on SO:

$ sqlite3 mydata.db ".dump" | sqlite3 new.db

Unfortunately, dumping this way excludes the table of records I'm looking for (Messages). Since I can get the format of the DB from Skype by just logging in with another account and generating a new main.db for it, do I have any additional options for extracting the contents of the corrupt DB? Failing that, is there a way to export the raw contents of the database in a text file or something? I only care about grabbing certain messages, which I can search for.

Upvotes: 2

Views: 4308

Answers (2)

Kumar Sandeep
Kumar Sandeep

Reputation: 11

First check for the PRAGMA integrity_check in command console and click on play button, note down the errors and repair them seperately or try exporting and then importing SQL file to new database and restart the database, it generally removes the slug files stored in the cache. If the above method does not work out then you can try SQLite database recovery tool https://www.recoveryandmanagement.com/repair-sqlite-database-manually/

Upvotes: 1

Steven Crain
Steven Crain

Reputation: 219

When the database is corrupt, the ".dump" command extracts all of the usable information, but then ends with a ROLLBACK because it encountered corruption.

Instead, store the output in a file:

$ sqlite3 mydata.db ".dump" > mydata.dump

Then, you can view the data directly in that file, or you can change the last line from "ROLLBACK" to "COMMIT" using a text editor. After that, you can load the valid portion of the data into a database using:

$ sqlite3 new.db < mydata.dump

Upvotes: 5

Related Questions