How to recover .sql files in Linux
 

Well, I had a major problem with my Ubuntu system yesterday and after trying many different things to fix it I have came to the realization that the system, along with all of my data, is lost.

I don't know what happened to the system, it just crashed and now it wont load. I booted up into a Live USB and thought I would be able to get my data back that way, but sadly not. The partition shows up as "unknown filesystem type" when I run the boot-info script. I tried to fix it with TestDisk but with no luck so I have given up on trying to sort it out and now just want some of my most important data back.

The most important of which are two sql dumps, about 2MB each, that I really need to get back. I have found a program called PhotoRec that I think will do the job. I have played about with it and it has succeeded in recovering my images and mp3's, all of which are totally useless to me without the file names.

So, I know the data is all there and I have not written anything to the drive since the problem arose. But the issue I'm having is that I can't see the option to recover the .sql files. I have downloaded PhotoRec 6.12 which the release documentation says has support for ".sql phpMyAdmin, MySQL and postgreSQL dumps".

But I can't find that option. Does anyone know how to recover the .sql dumps in PhotoRec or any other tool?

Thanks for any help.

If your mp3 files were properly tagged, you can automatically rename them based on the tags. There are various tools to do that.

Indeed it looks like despite the documentation, PhotoRec 6.12 isn't offering to recover .sql files. Maybe try compiling from source? (Perhaps it's a configuration option that wasn't used in the provided binary.) If that's no help you could always email the PhotoRec developer.

The key phrase "after trying many different things to fix it", if explained, might shed a light on things. That is if you have a list of things you did to (or inflicted upon ;-p) the system after the crash.


Could you run 'fdisk -l | tee fdisk.log' and 'testdisk /debug /log /dev/devicename' (where "/dev/devicename" is the device name of the whole hard disk) and attach the "fdisk.log" abd the one from testdisk?


cantab is right: if you build the tools yourself you need to have all the development libraries available at compile time.


The ".sql phpMyAdmin, MySQL" part you mentioned means different things: dot-sql is for SQLite version 3 databases (see the source) and MySQL in the case of Photorec means .frm and .MYI support only.

Photorec tries to find files in all or unallocated space by looking for headers and footers: simply said starting and ending lines of files that are characteristic for a file type. An example would be a static plain text HTML page because it starts and ends with the HTML tag. Some files don't have all required characteristics. For instance a JPEG image starts with "JFIF" but does not end with a specific footer. What a MySQL dump looks like depends on the tool you used. For instance if you used 'mysqldump' then if you used "-x" ("--xml") you got well-formatted XML but phpMyAdmin-driven dumps are plain text files but with a "phpmyadmin version x" header but no particular footer. What's more is that files may occupy space that is "linked" (secondary and tertiary blocks) and in all these cases Photorec will have to guess file boundaries. So the end results can not be guaranteed: for example you may end up with a 50 meg JPEG that on closer inspection also includes a copy of Something Completely Different but if your dumps are plain text it wouldn't be hard to edit them. (I suppose you didn't make any backups?)

Before or while you run 'photorec' to recover plain text files I suggest you recon the disk again, maybe in another machine if you suspect HW problems, because resurrecting the partition might just be the most efficient way.