Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I had some problems with my RAM (bluescreen several times, Windows XP) and now are my Firefox databases damaged. Firefox is working, but my history is gone and it's reporting several inconsistencies and errors when executing pragma integrity_check on places.sqlite:

database disk image is malformed

Now the question, how do I repair SQLite-Databases?

share|improve this question
For future reference, the FEBE (Firefox Environment Backup Extension) may be helpful in the future. Copies the entire profile, and packages it up as a single backup. I know it doesn't answer your question, but it may be helpful to know in the future. – Urda Feb 22 '10 at 15:03
Edited to help Googlers find this question. – bwDraco Dec 10 '14 at 20:37
add the schema version at the top of the SQL dump See here:… – Jason Clark Feb 5 at 13:34
up vote 13 down vote accepted


Because Firefox must be closed to perform this procedure, be sure to open this page in another web browser or print it out before proceeding.

After hours of work trying to recover the Places database, even reading the Firefox source code, I've managed to succeed. Here's how I did it:

  • Download the latest version of the SQLite shell and extract it into your profile folder. On Windows Vista and Windows 7, it is in the C:\Users\<username>\AppData\Roaming\Mozilla\Firefox\Profiles\<code>.default folder.
  • Close Firefox if it is running.
  • The Places database is in the places.sqlite file. If the file was replaced due to corruption, use the places.sqlite.corrupt file for recovery. Create a backup copy of the file, named places.sqlite.bak or places.sqlite.corrupt.bak.
  • Use the SQLite shell to open the database file (sqlite3 places.sqlite or sqlite3 places.sqlite.corrupt), then enter:
.output dump.sql    -- sends output to file dump.sql
.dump               -- dumps database to file
  • Because the database is corrupt, the resulting database dump is not complete, and not all of the recoverable data have been retrieved. To determine where the error occurred, search for the word ERROR (all caps) in an SQL comment inside the dump file dump.sql (I used Notepad++ to do this), and read the SQL INSERT command above it to determine the table in question. In my case, the damaged table is moz_places. (A description of the tables found in the Places database can be found here; a rather outdated ER diagram can be found here. I'll explain how to recover additional data from this table only; the following procedure is probably not applicable for the other tables, so skip these sub-steps if a table other than moz_places is involved.)

    • Each row in the moz_places table has an ID. The rows are dumped from the table following the order of this ID.1 The ID is the first value following the opening parenthesis in the INSERT statement. The area where the database is damaged is likely to be a small block of rows in this table; the idea here is to skip this damaged area and recover as much data as possible. The start area of such a block is represented in the dump as the row before the ERROR comment appears. Using the ID for this row, we can determine where the database is damaged. We do so by using SELECT statements with the ID as a condition; this process takes some trial and error. For example, if the last ID before the error was 49999, and the error follows, the damaged block starts at ID 50000. Use statements like:

    -- suppress unnecessary output
    -- the following command is for Windows systems
    -- for Linux and other Unix and Unix-like systems, use .output /dev/null
    .output NUL
    SELECT id FROM moz_places WHERE id >= 50100;
    • Adjust the value following the id >= and repeat the above SELECT command until you find the smallest value that does not cause SQLite to output an error. This is the ID that refers to the row starting from which we can recover additional data. Let's assume this ID is 50200. To dump this data, enter:

    .output dump2.sql
    .mode insert
    SELECT * FROM moz_places WHERE id >= 50200;
    -- restore normal output behavior
    .output stdout
    .mode list
    • Note that the INSERT statements in the dump2.sql file begins with INSERT INTO table VALUES, so use the find and replace feature in your text editor to replace all instances of this string with INSERT INTO moz_places VALUES.
    • Copy the entire contents of the dump2.sql file and paste it into the dump.sql file where the ERROR comment appears.
  • Replace the ROLLBACK; -- due to errors at the end of the file with COMMIT;.
  • Add the following code to the top of the dump.sql file. Replace <version> with the correct value, which is required for Firefox to determine the database schema version based on the version of Firefox, as follows (this can be found in the Firefox source file toolkit/components/places/Database.cpp):
    • Firefox 3: schema version 6
    • Firefox 3.5: schema version 8
    • Firefox 3.6: schema version 10
    • Firefox 4-7: schema version 11
    • Firefox 8-10: schema version 12
    • Firefox 11: schema version 16
    • Firefox 12: schema version 17
    • Firefox 13: schema version 19
    • Firefox 14-21: schema version 21
    • Firefox 22-23: schema version 22
    • Firefox 24-33: schema version 23
    • Firefox 34-35: schema version 24
    • Firefox 36: schema version 25
    • Firefox 37: schema version 26
    • Firefox 38: schema version 27
    • Firefox 39-40: schema version 28
    • Firefox 41: schema version 29

PRAGMA page_size=4096;
PRAGMA user_version=<version>;
  • Exit the SQLite shell, delete places.sqlite, then start the SQLite shell creating a empty places.sqlite database using sqlite3 places.sqlite. Type .read dump.sql to load the SQL dump into the database.
  • Start Firefox and confirm that your history and location bar are functioning as intended. Once you have confirmed that everything is OK, remove the database dump files and SQLite shell executable from the profile folder.

More relevant information can be found on the following pages:

1 SQL does not normally guarantee that database output will be given in any order unless you use the ORDER BY clause. However, ORDER BY will likely fail to produce any output on a corrupted database (as SQLite will need to read the entire table before it can produce any output). As far as I know, Firefox always writes moz_places table entries with sequential IDs, so we can assume that all output is ordered by ID.

share|improve this answer
This is pure awesomeness. Helped me recover almost all the history from a corrupt places.sqlite. Thanks a lot!! – Ashutosh Jindal Jan 10 '14 at 23:27
It did help, with two modifications: 1) add a ";" in the user_version line; 2) for some reason, my "corrupt" file had a schema version that was "one less" than expected. After your method didn't work initially, I tried importing the dump into the 10MB new database and failed because the old table had one column less. A look at the source code link made me understand what was going on. Awesome post!!! – Tilman Hausherr Dec 9 '14 at 23:37
@TilmanHausherr: Addressed. To avoid the column change issue, be sure to follow the steps in this answer as soon as you notice corruption and before updating Firefox, so that the database schema isn't changed. You could also try setting an older schema version—Firefox will update it to the new version when you restore the database. – bwDraco Dec 9 '14 at 23:38
Setting the previous schema version is what I had done when writing my first comment, i.e. I was already successful :-) Yeah, I suspect that I hadn't noticed the corruption immediately, I usually notice it only when entering characters that should make an "old URL" appear and nothing happens. – Tilman Hausherr Dec 9 '14 at 23:49
Excellent work! Glad you updated it, which put it back in the active questions where I spotted it. – fixer1234 Mar 23 '15 at 6:47

Well, depending on how damaged it is, repair might not be possible. Your best bet is probably to try and dump the db using sqlite, then see what you can salvage.

If that fails, you'll probably have to restore from backup.

share|improve this answer
Thank you. The SO post wasn't helpful since it didn't work, but the solution referenced in the link did work d:\sqlite3.exe d:\ .dump | d:\sqlite3.exe d:\ All favicons are now gone, but I they're rebuilding as I visit the sites. Thanks again! – Bobby Feb 23 '10 at 8:14… link in above question, was voluntarily removed by it's author. The answer below may be of help. – user66001 Feb 18 '13 at 19:41

As always with performing a repair like this, I recommend that you first make at least one backup copy of your places.sqlite file located in your profile directory. Having a backup allows you to try various different things to repair such problems while knowing that if the attempted repair makes things worse, you can always make another copy of the backup on which to try again.

Depending on what is corrupted and how badly it is corrupted, it may be possible to fix the problems with the extension Places Maintenance. I have ended up with a corrupted places.sqlite file on a few occasions. Places Maintenance has been able to fix the problem each time by running various of the checks/fixes which it provides as operations in its options dialog. The various different checks and/or reporting should take only a few moments to minutes.

If this does not work, then going the route of manually fixing it in a manner similar to what DragonLord describes above may be what is needed.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.