[sqlite] Restore SQLite DB from WAL
Hi, everyone Is it possible to restore deleted rows or dropped tables provided the WAL and shm files are still intact and contain the data you want to restore? Example scenario where vast swathes of content have vanished from mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty Android SMS app: ls -al /data/data/com.android.providers.telephony/databases/mm* -rw-rw 1 root root 60416 Jul 17 20:16 mmssms.db` -rw-rw 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm -rw-rw 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal TIA, Mandy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
On Mon, Jul 23, 2012 at 12:10 PM, gsm-gin...@gmx.ie wrote: Hi, everyone Is it possible to restore deleted rows or dropped tables provided the WAL and shm files are still intact and contain the data you want to restore? The WAL file does not contain the old data, it contains the new data. If the WAL file has not been checkpointed, you should be able to simply delete the WAL file and the old data will still be in the original database file. A checkpoint operation is the act of moving the new database from the WAL file back into the original database file, presumably overwriting the old data (depending on your settings and other factors). But, if the WAL file has been partially checkpointed, and that checkpoint was interrupted by a crash or power failure, deleting the WAL file will corrupt your database. So you should probably run the experiment on a backup. :-) Example scenario where vast swathes of content have vanished from mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty Android SMS app: ls -al /data/data/com.android.providers.telephony/databases/mm* -rw-rw1 root root 60416 Jul 17 20:16 mmssms.db` -rw-rw1 radioradio32768 Jul 17 16:18 mmssms.db-shm -rw-rw1 radioradio 628832 Jun 30 19:23 mmssms.db-wal TIA, Mandy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
So close, yet so far! This is really getting on my wick... Example scenario where vast swathes of content have vanished from mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty Android SMS app: ls -al /data/data/com.android.providers.telephony/databases/mm* -rw-rw 1 root root 60416 Jul 17 20:16 mmssms.db -rw-rw 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm -rw-rw 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal The WAL file does not contain the old data, it contains the new data. If the WAL file has not been checkpointed, you should be able to simply delete the WAL file and the old data will still be in the original database file. A checkpoint operation is the act of moving the new database from the WAL file back into the original database file, presumably overwriting the old data (depending on your settings and other factors). But, if the WAL file has been partially checkpointed, and that checkpoint was interrupted by a crash or power failure, deleting the WAL file will corrupt your database. So you should probably run the experiment on a backup. :-) In my test, I started the phone in single-user/recovery mode, deleted mmssms.db-wal and restarted. The deleted messages showed very briefly, then vanished. I then restored mmssms.db to its original (just after deletion) state and deleted both mmssms.db-wal and mmssms.db-shm. Same result. Not to be deterred, I then restored the mmssms.db, deleted the mmssms.db-shm and created a zero-length file. In all cases the owner was set to radio:radio (in the initial example below mmssms.db was incorrectly set to root.) In each case the same thing happens, the messages briefly display, then disappear, mmssms.db, then mmssms.db-wal and mmssms.db-shm with the much sought-after deleted messages, so (if I'm not mistaken) at least some of the info is retained in mmssms.db itself, albeit with instructions to purge. Is there any way to merge the lost data stored in mmssms.db-wal / mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a bunch of DELETE FROM statements could they be changed to INSERT INTO? Mandy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
On Mon, Jul 23, 2012 at 3:51 PM, gsm-gin...@gmx.ie wrote: Is there any way to merge the lost data stored in mmssms.db-wal / mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a bunch of DELETE FROM statements could they be changed to INSERT INTO? The WAL file does not store a bunch of DELETE FROM statements. The WAL file stores 4k pages of the database file as they will appear after the delete occurs. Details at http://www.sqlite.org/fileformat2.html So, no, there is no way to do what you are asking. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
The WAL file does not store a bunch of DELETE FROM statements. The WAL file stores 4k pages of the database file as they will appear after the delete occurs. Details at http://www.sqlite.org/fileformat2.html So, no, there is no way to do what you are asking. Damn! Failing that, is there a means of extracting the data from the WAL / SHM by some similar means to extracting from the DB? Thanks Mandy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
In each case the same thing happens, the messages briefly display, then disappear I think your sms-controlling app has synced the whole sms database with some server. When you start your phone it shows you local data but then it sees that server has latest data (maybe using modification date on the database, maybe some synchronization token stored in the database) and restores everything from there. So maybe you need to look for some setting saying allow to sync everything with server and allow to sync everything back from server. Pavel On Mon, Jul 23, 2012 at 3:51 PM, gsm-gin...@gmx.ie wrote: So close, yet so far! This is really getting on my wick... Example scenario where vast swathes of content have vanished from mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty Android SMS app: ls -al /data/data/com.android.providers.telephony/databases/mm* -rw-rw 1 root root 60416 Jul 17 20:16 mmssms.db -rw-rw 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm -rw-rw 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal The WAL file does not contain the old data, it contains the new data. If the WAL file has not been checkpointed, you should be able to simply delete the WAL file and the old data will still be in the original database file. A checkpoint operation is the act of moving the new database from the WAL file back into the original database file, presumably overwriting the old data (depending on your settings and other factors). But, if the WAL file has been partially checkpointed, and that checkpoint was interrupted by a crash or power failure, deleting the WAL file will corrupt your database. So you should probably run the experiment on a backup. :-) In my test, I started the phone in single-user/recovery mode, deleted mmssms.db-wal and restarted. The deleted messages showed very briefly, then vanished. I then restored mmssms.db to its original (just after deletion) state and deleted both mmssms.db-wal and mmssms.db-shm. Same result. Not to be deterred, I then restored the mmssms.db, deleted the mmssms.db-shm and created a zero-length file. In all cases the owner was set to radio:radio (in the initial example below mmssms.db was incorrectly set to root.) In each case the same thing happens, the messages briefly display, then disappear, mmssms.db, then mmssms.db-wal and mmssms.db-shm with the much sought-after deleted messages, so (if I'm not mistaken) at least some of the info is retained in mmssms.db itself, albeit with instructions to purge. Is there any way to merge the lost data stored in mmssms.db-wal / mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a bunch of DELETE FROM statements could they be changed to INSERT INTO? Mandy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
I think your sms-controlling app has synced the whole sms database with some server. When you start your phone it shows you local data but then it sees that server has latest data (maybe using modification date on the database, maybe some synchronization token stored in the database) and restores everything from there. So maybe you need to look for some setting saying allow to sync everything with server and allow to sync everything back from server. Pavel Quite possible, although AFAICT this is not the default behaviour unless the SMS Backup / Restore is installed and set to sync to GMail. This would be consistent with the fact that data was turned off for the entire accidental delete, imaging and restore attempt. Thanks anyway, though :) M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Restore SQLite DB from WAL
The WAL file does not store a bunch of DELETE FROM statements. The WAL file stores 4k pages of the database file as they will appear after the delete occurs. Details at http://www.sqlite.org/fileformat2.html So, no, there is no way to do what you are asking Is there, instead, a method for extracting the pages marked for ignore from the DB/ WAL / SHM files and manually piecing back together, or issuing a directive to alter the relevant page markers from ignore to their previous state? This article implies there is: http://digitalinvestigation.wordpress.com/2012/05/04/the-forensic-implications-of-sqlites-write-ahead-log/ That said, I'm guessing it would be a non-trivial task even for a seasoned DB programmer... The only other way I can see out of it would be to create a dummy SMS DB (e.g. with one test SMS), then use this method to create a XML template to use with SMS Backup / restore, manually piecing the XML file togther using data extracted with carefully-chosen* GNU strings parameters: http://forum.xda-developers.com/showthread.php?t=1585957 http://forum.xda-developers.com/showthread.php?t=1683608 That or stump up the 250 GBP for Epilog SQLite analysis ;) M *At least it looked that way when running the SHM file through strings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
