Restore a broken Skype history (main.db)
Having once again bothered to restore the broken base, I decided to sketch a brief instruction on how I managed to get back almost the entire history of Skype messages. Connoisseurs of sqlite3 are invited to write better ways than I felt at random.Prehistory
Reinstalled Win7 OS, installed skype (6th), habitually copied the entire profile folder from the old one:
% AppData% \ Roaming \ Skype \ my_profile \
to a new place.
I launch Skype and suddenly it hangs on the auto-login. After restarting, I see an invitation to enter. I enter - all contacts are in place, and there are practically no messages anywhere. Only in some group chats are preserved. All personal correspondence, which has accumulated a lot - disappeared. I made N (^ k) attempts to copy the database, deleting lock files, is-corrupt and others. Skype swears that there is a problem with the base, then asks for a restart - after which there are no messages.
Googling, found habra posts about exporting messages , hijacking accounts , etc. Yeah, mean sqlite! It is encouraging.
Tools and materials
You will need:
- the surviving copy of the main.db database before it was “opened” by new Skype, completely erasing the data (or take the main.corrupt file if there was no copy)
- SQLite Manager
- sqlite3 command line (optimally - work in it under * nix)
- Notepad ++ or any other editor that does not crash UTF-8 files.
- * nix shell (some server with linux / ubuntu / ..., since the most important last step under Windows may not work)
')
Analyzing the base
Information for admins, programmers and advanced users
1. Swing SQLite Manager (thanks for the link, minamoto ).
2. Open the file main.db (about 150 megabytes for me).
3. Database - Integrity check - Full check.
4. I execute the specified command, I get a lot of scary beeches:
By the way, in other utilities, when trying to view message tables, I often received this: " the database disk image is malformed ".
2. Open the file main.db (about 150 megabytes for me).
3. Database - Integrity check - Full check.
Check the integrity of the database using the "PRAGMA integrity_check".
Result: NOT SUCCESSFUL. To see errors, execute the PRAGMA integrity_check operator in the Run Request tab.
4. I execute the specified command, I get a lot of scary beeches:
"*** in database main ***
On tree page 5482 cell 13: Rowid 262638 out of order (max larger than parent max of 255232)
On tree page 22553 cell 8: Rowid 255233 out of order (min less than parent min of 262638)
On tree page 5601 cell 10: Rowid 270500 out of order (max larger than parent max of 255358)
On tree page 9610 cell 10: Rowid 255359 out of order (min less than parent min of 270500)
On tree page 25320 cell 145: 2nd reference to page 5482
On tree page 25320 cell 145: Child page depth differs
On tree page 25320 cell 146: Child page depth differs
On tree page 25827 cell 290: 2nd reference to page 5601
On tree page 25827 cell 290: Child page depth differs
On tree page 25827 cell 291: Child page depth differs
On tree page 8955 cell 0: 2nd reference to page 28154
On tree page 27843 cell 0: 2nd reference to page 28136
Page 5335 is never used
Page 5344 is never used
Page 5369 is never used
... there are many such never used
Page 28212 is never used "
“Wrong # of entries in index IX_Messages_remote_id”
“Wrong # of entries in index IX_Messages_timestamp_convo_id_type”
“Wrong # of entries in index IX_Messages_timestamp_chat”
“Wrong # of entries in index IX_Messages_call_guid”
“Wrong # of entries in index IX_Messages_convo_id_timestamp_consumption_status_sending_status”
"Rowid 95101 missing from index IX_Contacts_buddystatus"
"Rowid 103110 missing from index IX_Contacts_buddystatus"
"Rowid 209626 missing from index IX_Contacts_buddystatus"
By the way, in other utilities, when trying to view message tables, I often received this: " the database disk image is malformed ".
Since I am not an expert in sqlite and do not want to study every error, we proceed simply.
Treatment
Google how to dump the sqlite database, it turns out like this:
1. Download sqlite3 command line for Windows or “apt-get install sqlite3” for * nix. I first experimented under Windows, but later I had to go to the Linux console, because Windows sqlite3 not quite well proved.
2. Under Windows, copy sqlite3.exe to a folder with a copy of the database (archived from the previous Windows, which Skype has not yet spoiled). Then run cmd (Start> Run).
Under Linux, we simply execute:
cd ------sqlite3.exesqlite3 main.db .dump>>myDumpSQLite.sql(It is “main.db [space] .dump, do not mix it up)
3. Open the file myDumpSQLite.sql in a normal text editor, for example Notepad ++ . At the very end of the file I had a command:
ROLLBACK;Who is familiar with SQL, knows why you need to delete this last line in the file and instead write it:
COMMIT;(You can completely abandon the transaction, but then there will be a very long import of data into the new database.)
4. Create a new, clean database from the file myDumpSQLite.sql (recommended for Linux).
sqlite3 main-recovered.db <myDumpSQLite.sqlError messages may occur.
I had it like this:
Due to the fact that we did COMMIT at the end of the file, errors will be ignored. Theoretically, something will be lost (some single messages, maybe even some chat will disappear). But this is nothing compared to the loss of the entire base that Microsoft offers.
Error: near line 329619: PRIMARY KEY must be unique
Error: near line 329620: PRIMARY KEY must be unique
Error: near line 329621: PRIMARY KEY must be unique
Error: near line 329622: PRIMARY KEY must be unique
...
Due to the fact that we did COMMIT at the end of the file, errors will be ignored. Theoretically, something will be lost (some single messages, maybe even some chat will disappear). But this is nothing compared to the loss of the entire base that Microsoft offers.
The resulting file main-recovered.db should weigh about the same as the sql file (I got 123MB). If you skip step 3 or something goes wrong, you will get an empty useless file.
Windows like. © CEP
I did it only under Linux, so if it doesn't work under Windows, and you don’t know linux, look for the right friends). Windows sqlite3 issued:
Error: incomplete SQL: INSERT INTO "Contacts" VALUES (951 .......
5. Copy the file to the folder% AppData% \ Roaming \ Skype \ my_profile \ under the name main.db, replacing the broken (Skype should be turned off).
6. Run Skype. Enter the username and password (I went to the machine).
7. PROFIT !!! All messages are back!
Know the best way? - I invite in the comments.
Perhaps there is a more competent way to restore the database. I even found Some greedy utility , but it did not help me (maybe I had to give money?). I also tried SkypeHistoryReader, Kudos Chat Search, sqlite maestro and all sorts of different things.
Source: https://habr.com/ru/post/160629/
All Articles