3

I want recover deleted record and dropped data from SQLite db file,

I check my db file with text editor and i saw dropped data is still there..

I'm using SQLite3.dll (in c#) but i can`t find something property for open db with dropped data or ...

Is there any library for helping me? or something simple code for recovering data from sqlite db file?

Edit 1:

i try to find a data recovery library or program for sqlite.. after many searchs i found http://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteDump.html

SQLiteConnection.Open(); 
SQLiteDump sqSqlDump = new SQLiteDump(); 
sqSqlDump.Connection = SQLiteConnection1; 
sqSqlDump.IncludeDrop = true;                          //<<-- check here
sqSqlDump.Backup(); 
StreamWriter stream = new StreamWriter("d:\\dump.dmp"); 
stream.WriteLine(sqSqlDump.DumpText); 
stream.Close();

that i can use IncludeDrop property for backing data to me but this library is not free and i cant use it.. i like to find free library or program...

Mehdi Yeganeh
  • 2,019
  • 2
  • 24
  • 42
  • 1
    If your time is worth less than your money, you have to read the [file format documentation](http://www.sqlite.org/fileformat2.html) and extract the data by hand. – CL. Feb 07 '14 at 13:22
  • 1
    @CL, tanx.. its good for me.. my problem is i living in country that i haven`t access to visa card or online buying or .. and i should write it and not buy it.. ;) – Mehdi Yeganeh Feb 07 '14 at 13:25

3 Answers3

3

SQLite deletes data perminently when you delete records. It would be up to you to implement a model where by data was only marked as deleted or maybe a system where you store all versions of the data. You do this by adding fields to mark those attributes. Then when you do selects you change them to account for the new fields functionality.

Like:

select * from mytable where mytable.deleted<>true and etc=etc

SQLite stores all it's data in a single file. If you have a file system backup service in place you could get at old versions of the database.

When SQLite deletes records there maybe ghost data left in the file. This data is just rubbish and won't allow you to reliably get back to a previous point before deletion.

The SQLite db file is binary, be careful editing it with a text editor if you save it will be corrupted!

AnthonyLambert
  • 8,768
  • 4
  • 37
  • 72
  • tanx for helping me but i need to recovery library and i want to open db file that i didn`t wrote program.. please check Edit1 – Mehdi Yeganeh Feb 07 '14 at 13:20
  • because SQLite deletes by writing new b-trees, and clustered index records etc.. you may find some fragments of what was there but it is likely most of the data will just be gone forever. If they deleted a large proportion in one go then your are more likely to see that remaining in the file. – AnthonyLambert Feb 07 '14 at 15:25
  • 1
    "most of the data will be gone" - that is not true. I just accidentally dropped a table with ~15k rows, and almost everything in it can still be seen when opening it with an editor. Restoring this might be hard, yes – phil294 May 23 '16 at 12:04
  • @Blauhirm did you read my comment above yours fully? confused! – AnthonyLambert May 25 '16 at 09:11
3

It is possible, at least partially, by hiring a (very expensive) data recovery firm which would recover the data by analysing the binary file. You will probably not be able to do it yourself because, as others have said, "SQLite deletes data permanently when you delete records" and that is true, because the data deletion operation in SQLite involves overwriting the metadata in the file describing where the data is and how it is related to other data.

Parts of the data might still physically be in the file but:

  • The pointers to this data (sqlite uses btrees) are gone and the file regions are marked as "free space" in the metedata
  • In the process of using the database, some of that space may have been overwritten and now contains garbage.

If you have have nothing to do for the next couple of months, you may start by analysing the SQLite file structure and applying that knowledge to try guessing where the data might have ended up in the file before it was deleted (meaning, its metadata was overwritten) and so perform the recovery. You need to start here: http://www.sqlite.org/fileformat.html .

Ivan Voras
  • 1,895
  • 1
  • 13
  • 20
  • I _really_ wouldn't rely on a data recovery firm when dealing with any database, as DBs tend to reuse disk pages relatively quickly. – Donal Fellows Feb 07 '14 at 15:31
0

I don't think that is possible, see Documentation. I don't know what data you are seeing in your text editor.

initram
  • 46
  • 5