SQLTeam.com | Weblogs | Forums

I've no idea what to do with my .dbf

I like to use FoxPro 9, cause of it is suitable for my job. But there is a problem last days. I saw a message - Error closing file. One of my friends on job, tried to solve it, but there wasn't success. Guys, I hope you can suggest something.

First thing to do: Get rid of FoxPro! It is no longer supported. if you get into trouble (like this), Microsoft won't help you no matter how much money you give them.

Second, is that the whole error message? It says so little! Perhaps the dbf is also open on another machine?

If my database was trashed, regardless of whether it was Foxpro, or something else, my todo list would be:

Restore from backup. If that is straightforward (e.g. it causes no loss of work/data) then do it in production.;

Also: Restore from backup to a clean machine. i.e. this assumes that the primary machine has a fault, which has caused the corruption to the database. For example a [now] faulty Disk Controller has randomly written garbage into the database file(s), or a powercut has trashed something.

On that other machine run whatever tools are available to test the "consistency" of the data. I don't know what is available in Foxpro, but for example the data records can be read end-to-end, sequentially, to check that the whole file can be read. A decent system will include Checksums etc. on each record/block to prove that the data has not got corrupted. Also comparison of the actual data against the data that is stored in indexes for that record, if they are different/missing that is an indicator that something is wrong.

Assuming that the "test on other machine" finds that the backup file is clean (and, ideally, you also checked a more recent backup, or a copy of the live database, and you DID find a fault with that) then you definitely have a problem that needs solving. Before doing anything figure out what that problem is (e.g. the fault disk controller in my earlier example) and get it fixed. OTHERWISE the fault will reoccur ... soon! ... and users will lose data all over again when you restore, again, to an old copy.

Or move the database to another server and operate off that until the fault is found & fixed.

Once you've sorted the problem out then have a post mortem:

  1. How did this problem occur? If it was a hardware fault then you can't proactively do much about that (but see #2 below). If it was human error (Accidental deletion of all customers who's names start with "N") then plug that hole.

  2. How can I sooner if a fault develops in the future? Restore your Daily Backup to another computer EVERY NIGHT and run a data consistency check on it, so you know within 24 hours if there is corruption.

Or perhaps there is a setting in your database that allows checksum checking a) in real time and b) every time a backup is made? Perhaps that is not turned on? Why not?

MS SQL has that capability. Its turned off in lots of places that i visit. Absolutely crazy.

Further options:

You don't have a usable backup (e.g. the backups are also corrupted, or too old, or you don't have one at all - Seriously? Why not?). Suck it up, your data is gone. Start over, fix the problem then resign so that someone more competent can be employed to look after the data.

You think the data might be recoverable from the file, and you would prefer to do that rather than restore from backup. In that case pay a Data Recovery Specialist to do the recovery. Maybe your office insurance policy will cover it. But you are going to down for SOME TIME (days, maybe weeks) until you get a cleaned up database back from the Data Recovery people, what are you and your business going to do in the meantime?

Spend the time figuring out how to stop it from ever happening again, and making sure your backups, and your procedures for CHECKING that your backups ARE working, never, ever, allow you to be in this position again.

Foxpro is out of support? So should your systems be too. If the main product you are using is no longer supported you have no excuse for continuing to use it, annoying as it may be, so move to something else which is supported. If it is not supported its probably for one of two reasons:

  1. The company went bust. Tough, sorry to hear it, move on.
  2. Something better exists and the old product was retired. OK ... so you are happy still using the inferior product? Why?

Here's what you get with MS SQL:

Realtime checksum validation on "data pages" in the database.

Similar validation when a backup is made.

Ability to run a Data Consistency Check on the database. Including restoring the database from backup onto A.N.Another machine and testing it there (i.e. if that is clean that it guarantees that the original database, from which the backup was made, was also clean at that time)

Log Backups. Restore to any point-in-time. You discover that a user accidentally deleted all all customers with names starting with "N" at 12:42 on Monday - fine, restore to 12:41. Either all data SINCE then is lost, and you recreate it, or restore to a new, temporary, database and copy-across all the missing customer records to the Production database, then delete the temporary database.

Make log backups every couple of minutes, then your worst case loss is 2 minutes of work.

But in practice, if you keep the logs on a different drive to the data, it is very unlikely you will lose both Logs and Data at the same time (unless server / building is destroyed), and if the Logs are still OK you can make one final, "Tail", log backup and then restore up to that final log backup which will mean you have NO data loss.

Or, if your data is mission-critical to the business, then have a pair of servers configured to run such that if one fails the other just takes over. Or a cut-down version of that which allows the "secondary" to take over after a short delay (cheaper to implement for companies that don't need instant switch-over after a failure).

Crazy to be using out of date technology for a database that holds data that is important - for whatever your definition of "important" is.

  1. restore to excel
  2. restore to Access
  3. Move to SQL Server