Real Time Backup of MDE? 2

[rdpress]

Currently I’m backing-up the back-end of my database every 30 minutes. We’re using backup software that simply copies then mbe file every 30 minutes. Up until now we could live with the potential of loosing up to 30 minutes of work. We are now considering adding a feature that is very critical – I cannot loose any of the data.

Any suggestions as how I can back-up changes real-time? I’m wondering if there are 3rd party add-ins that may help.

The back-end has around 180 separate tables, of which maybe a dozen are part of the critical feature. I have about 10 users on the database and the most active table has about 400 transactions a day.

 

[lameid]

I don't think there is any thing as not ever losing any data.

Although SQL server is a more robust package natively, even it can conceivably fail.

 

[MichaelRed]

About the best I can concieve is a transaction record (in a seperate database). There are two conceptual approaches to this.

1)[tab]When any record is saved, save a copy of the record (along with the origin (e.g. RECORDSET name) and a time stamp to the seperate / external data base (as well as other useful information (e.g. user name).

2)[tab]When any record is saved, check the Dirty attribute of the FORM and if this is true, compare each (bound) control's current vlue to its old (previuos) value. If these are not equal, save the relevant information to the seperate and external data base.

In Ms. A. both of these depend on the sole and exclusive use of FORMS to manipulate the data, as there is no other mechanisim to determine wheather a value has changed.

Obviously, neither approach will be of any use where external data is (programatically) added (imported) to the dataset.

Ther is an example in the FAQs (faq181-291)for the latter approach and I am sure that there is one or more examples of the first approach in these (Tek-Tips) fora as well as in the general realm.

MichaelRed

 

[lameid]

Backup and Recovery ultimately come down to two things...

How much will it cost to lose the incremental information?
How much will it cost to ensure the incremental information is not lost?

While MichaelRed's post is the best all Access solution, Access is still prone to corruption or users deleting the MDB.

With a database server such as SQL server you gain the ability to generate and save transaction logs that can be rolled forward or backwards to accomodate mistakes. You can also cluster them and I believe replicate them. This may vary by product but both SQL and Oracle should have robust solutions or supplementary products possibly from other vendors.

Is you concern High Availability, data loss or both?
When you say new feature of no data loss, what kind of ensurance are you wanting to afford? One SQL server on the low end is probably about $5500... I'd probably plan on spending $10,000. And then you might be talking another outlay depending on how good of back up you really want.