How to retrieve your Firefox bookmarks from a backup

In my latest post I wrote a long rant about why I don’t like Firefox. Actually, after my rant, I started analyzing what was wrong with my PC. The answer was promptly received: I was using the incorrect version of the operative system. So, in a brilliant spur of the moment, I returned to Windows XP. It took a whole day to set everything up, and I had lots of problems, but what’s worth is that my system is back into commission, and with better performance but less eye-candy.

And Firefox is still here, making himself comfortable, even if I don’t want him to be. I’m slowly trying to migrate to my new choice, after I set it up the most I can as Firefox. However, as always, I did a backup of my personal files, game save states, fonts, sounds, cursors, etcetera, before reinstalling Windows, so that I don’t lose them during the downgrade.

Despite my backup, Firefox is a weird app. Since the version 3.0, Firefox uses SQLite to store your personal profile information: bookmarks, configurations, everything. That makes it weird to work with a backup. The generic process to restore a backup is to copy-paste all the session files from the backup to the Firefox profile folder in Documents and Settings (2000/XP) or Users (Vista/7). But I don’t want to restore the whole session! I only want my bookmarks, in a list! That’s when the wheels started running.

SQLite is a pretty standard format, thus, any SQLite editor/viewer works. In my case I searched for one that was portable, so that I don’t have to install it. I found SQLiteSpy, a dependence-free application, that allows you to access SQLite 3 databases, and run queries on their info freely. The app is free for personal and educational use.

SQLiteSpy main window, after opening my bookmark file.

Now then, after running it, we simply clic on File and then Open Database… In the Open box we go to the next path, depending on our backup and operative system.

In Windows XP: <User profile folder>Application DataMozillaFirefoxProfiles<some characters>.<Firefox profile name>.

En Windows Vista o 7: <User profile folder>AppDataRoamingMozillaFirefoxProfiles<some characters>.<Firefox profile name>.

In my case, my whole backup path was G:ABCTamashiiAppDataRoamingMozillaFirefoxProfiles_2zni7hoct.Default. Your’s should be pretty different.

Inside that folder we should open the file named Places.sqlite. By default SQLiteSpy doesn’t shows files with a file extension different than DB or DB3, so, in the Open box we can filter with Files of Type: Any Type. Once you found it, you click on Open. The file has several tables, but I’ll only go into detail with two of them: moz_places and moz_bookmarks. The first table, moz_places, is the general storage of all the visited URLs, stored by the History system, including some stats and additional information. The second table, moz_bookmarks, contains information about which of those URLs have been configured as bookmarks, including the bookmark title and more information.

Now, knowing this information, how can you retrieve your bookmarks and their titles? Very simple. In the upper section of the SQLiteSpy window there is an edit box. You can write a SQL query there, and the program will execute it. The query you have to write is:

SELECT bm.title, pl.url FROM moz_bookmarks AS bm, moz_places AS pl WHERE bm.fk = pl.id;

SQLite just ran my query... And I obfuscated some of my entries there for security.

Basically, all this query does is something like “Show me the title and URL of the rows of both tables which id is equal in both.”. To run it, click on the Execute menu and then choose the first option, Execute SQL. The table view will update it self to show the stored information. Some of the entries will look odd, or like you never created a bookmark for them. That’s because Firefox stores the RSS feeds as bookmarks, so they will show up there if you were subscribed to any of them (in my case, they just popped up there).

The process is painless and pretty useful. You can then go to the Edit menu, choose Select All, and then Copy, and there you have your bookmark list, completely ready (tab separated) to be pasted in a spreadsheet, or a text file, or in any other place. It is more convenient than installing Firefox and creating a new session just to see those bookmarks… And then, finding out how to export them.

For expert users: There are several other fields in the moz_places table, in example a simple visit counter (visit_count) and a ranking that the Firefox designers created, named Frecency. Basically, it’s an algorithm that tries to measure how frequently a page is visited, how recently, and from where. It gives out more weight to writing the URL in the Address Bar, than to click in a bookmark, than to visit it from other page. Frecency helps the browser know which pages to recommend when you write something in the Address Bar.