Jump to content


Photo

Corrupt Database, Backups & Recovery Therefrom


  • Please log in to reply
28 replies to this topic

#21 Emilito

Emilito

    Member

  • Members
  • PipPip
  • 9 posts

Posted 14 June 2010 - 07:00 AM

Above link is failing because the trailing period is being included in the URL. Here's a working link:

http://kb.parrallels.com/en/8296



#22 Emilito

Emilito

    Member

  • Members
  • PipPip
  • 9 posts

Posted 14 June 2010 - 07:37 AM

As the person that first reported this problem, I can report that Parallels WAS the culprit... And to the comment something to the effect that "I don't see why old backups were corrupt," this was an insidious bug: when I restored those (good) backups, the resulting db was immediately corrupted. I installed the suggested fix (thanks to kbens0n for explaining that the trailing period was causing the link not to work; I hadn't figured that out). Yesterday I was able to apply the fix and it worked. Ironically the fix was to an add-on to Parallels (Parallels Tools) not Parallels itself, so even after applying the fix there was no new version number, nothing to indicate Parallels was updated. Bottom line, however, it works.

Apologies to all those that were panicked by my issues; hopefully there aren't too many Parallels users that have had similar stories (though I can't imagine I'm the only one).

The guilty version was released in mid-March; I restored a backup from early March. Between that time and now I only added one person (and I know who that is) and possibly made a few minor changes to the db. In the end, for me this potential "life's work disaster" has ended up as a very nerve-wracking time-consuming issue with no significant data loss.

I am very surprised however, that this Parallels issue, potentially damaging to so many other users of Windows database products (Quicken, etc etc - apps that I also use), was so little known. And am thrilled that the info was posted here.

Finally, as we now know that backups cannot be verified till they are restored, what is the recommended procedure for assuring that backups are, indeed, good? This still has potentially very scary outcomes.

Emil

#23 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 10 March 2012 - 10:20 AM

An update to this thread is warranted, given that it began in the RootsMagic 4 era. With RootsMagic 5, we now have built-in SQLite database tools: Integrity Check, Reindex and Vacuum, accessible with name variances via the menu File > Database Tools as "Test database integrity", "Rebuild indexes", and "Compact database". While we now have built-in tools to check for database corruption and even recover from mild forms, the integrity check must be started voluntarily and the underlying causes remain the same. So the probability of corruption remains the same and early detection is unchanged if you do not use the tool. What has improved is the potential to recover from mild corruption but anything more serious still requires special procedures and tools to accomplish even a partial recovery. We will continue to hear occasional reports of "RootsMagic has encountered an unexpected error: SqLite Error 11- database disk image is malformed"

I have outlined how I try to recover a "malformed" database in this new page on the SQLiteToolsForRootsMagic wiki: Corrupt Database Recovery.However, frequent backups and integrity testing are a far better strategy than gambling that some SQLite geek will be able to bail you out. Early detection and rolling back to yesterday's backup is much easier, self-reliant and reassuring.

Here is an example of how things can go terribly wrong! A RootsMagic user embarked on an operating system upgrade which unexpectedly re-wrote his hard drive. He had no external backups. All of his RootsMagic backups were lost. He did have a copy of his database on a flash drive from which he transferred the database. However, through some weird combination of simultaneous transfer and updating of software, both copies got corrupted. This was a fairly large database: some 75MB, 80,000 persons - the bigger they are, the greater the exposure to corrupting influences. I used my recovery techniques and was successful with the 80,000 PersonTable. However, less than 5% of the NameTable was salvaged and worse, still, less than 200 events, most of which would be for nameless people! Thus, only a tiny fraction of the original database could be recovered. To go any farther would require a very deep understanding of the inner workings of SQLite along with hexadecimal editing of the database file - that's beyond me.

It is unfortunate that RootsMagic 5 does not offer the option of an "Automatic Database Integrity Test on Opening" - that would be a desirable enhancement. One can still do an automatic SQLite Quick Check using external tools by following the instructions in Check RootsMagic Database Integrity on Opening. And maybe the Quick Check is what should be in the enhancement, if the full check which includes rebuildable indexes takes too much time for users of large database files.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#24 mapleleaf

mapleleaf

    Advanced Member

  • Members
  • PipPipPip
  • 558 posts

Posted 10 March 2012 - 12:48 PM

An update to this thread is warranted, given that it began in the RootsMagic 4 era. With RootsMagic 5, we now have built-in SQLite database tools: Integrity Check, Reindex and Vacuum, accessible with name variances via the menu File > Database Tools as "Test database integrity", "Rebuild indexes", and "Compact database". While we now have built-in tools to check for database corruption and even recover from mild forms, the integrity check must be started voluntarily and the underlying causes remain the same. So the probability of corruption remains the same and early detection is unchanged if you do not use the tool.


Are you saying the doing File > Database Tools > Test database integrity would be enough to check for problems, if that was done every time that you started up the RM5 program? Or should we be doing all 3 items in the Database Tools window? I'm still confused.
~ Debbie

#25 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 10 March 2012 - 01:15 PM

I meant that the quick integrity check should be done each time a file is opened (or closed). Not necessary to do the others but occasionally.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#26 Ludlow Bay

Ludlow Bay

    Advanced Member

  • Members
  • PipPipPip
  • 868 posts

Posted 10 March 2012 - 02:21 PM

Are you saying the doing File > Database Tools > Test database integrity would be enough to check for problems, if that was done every time that you started up the RM5 program? Or should we be doing all 3 items in the Database Tools window? I'm still confused.


I meant that the quick integrity check should be done each time a file is opened (or closed). Not necessary to do the others but occasionally.


... but even that won't help much if you are one of those users who keeps your computer running and RM open for days & days at a time. You can lose a lot of newly-entered data before you realize you have a problem. I think that an integrity check should be run automatically - and not configurable otherwise - every hour that the program is open (we're talking less than a minute each time for most users). For those of you who don't like the forced imposition, you can cry "foul" now, or you can cry later ... when you've lost a lot of work.

#27 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 10 March 2012 - 03:20 PM

... but even that won't help much if you are one of those users who keeps your computer running and RM open for days & days at a time. You can lose a lot of newly-entered data before you realize you have a problem. I think that an integrity check should be run automatically - and not configurable otherwise - every hour that the program is open (we're talking less than a minute each time for most users). For those of you who don't like the forced imposition, you can cry "foul" now, or you can cry later ... when you've lost a lot of work.

That's a good idea - best handled from within RootsMagic but, I daresay, could be set up externally with Task Scheduler for those working on just one database file whose name never changes.

OTOH, I don't think you run greater risk of losing lots of data just because you leave the program running with the database seemingly 'open' instead of periodically closing and re-opening. RM does not store up data in RAM which it then writes to disk when the user 'closes' the database. Behind the scene, RM or, rather, its SQLite database engine is opening, reading, writing, closing the database file many times as you proceed to edit a Person.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#28 Ludlow Bay

Ludlow Bay

    Advanced Member

  • Members
  • PipPipPip
  • 868 posts

Posted 10 March 2012 - 03:38 PM

OTOH, I don't think you run greater risk of losing lots of data just because you leave the program running with the database seemingly 'open' instead of periodically closing and re-opening.


True, except that people just don't always do what they're told (make backups, run integrity checks) .... computers do :)

#29 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 8470 posts

Posted 12 March 2012 - 09:21 AM

Confirming enhancement requests are in our tracking system.
Renee
RootsMagic