Access database backups need advice

[sark666]

We have numerous access databases (kept separate to limit the amount of concurrent users) that are currently not backed up.

We are looking into getting an automated tape backup in place, but in the meantime I'm looking at creating a little backup utility.

Just recently, we had a database somehow end up completely empty. There are 4 tables in each database and the only table that retained info was the facility table. I have no idea how this happened but I know access can be somewhat unreliable under certain circumstances.

Luckily, I had a backup of this one facility but that was just by fluke.

So my question is, even if I write some batch script to backup the actual database files, that wouldn't have avoided this issue as the backup would be a copy of a blank database.

So I'm thinking on writing a vba excel utility that copies each facility's 4 tables to an individual excel file for each facility.

But I'm curious how others handle it. Is there a means of simply generatign a checksum? Some other means of verifying the actual access database integrity instead of always copying it's contents?

Again, I would think the norm would be to copy the actual database file but as I mentioned that wouldn't have avoided this issue. The backup I had was two weeks old so it happened sometime in a two week span, so even a 7 day backup might not have avoided this.

So any suggestions would be appreciated.

 

[xenou]

Any backup system is limited by the state of the file at the time of backup. If the backup of 7 days ago doesn't help, you would go to the backup of 14 days ago. I would just backup the Access files, using standard backup tools, or your own backup script, on a schedule of your choosing (e.g., hourly, daily, weekly, monthly ... ).

Typically, one keeps backups on a rotating basis so that there's more than one - you may also save permanent backups on a monthly or yearly basis. Backup utilities have the advantage of being able to create incremental as well as full backups.

If you used a DB with a lot of transactional data, you could probably just save (or duplicate) the critical data rather than the whole DB file - I typically use Access for analysis rather than actual operational processes so my backup interests are usually not mission critical but just about not losing my work.

But I'm curious how others handle it. Is there a means of simply generatign a checksum? Some other means of verifying the actual access database integrity instead of always copying it's contents?

Seems to me it would do you no good to find the database has left Kansas but not to actually have a backup of it ... in any case if the database is being used, a checksum would constantly be changing (this might something that would apply to a front-end database that does not change - or you could just wait for the phone call).

 

[dogdays]

I back up to CD/DVDs, usually daily. I prefer this method to using tape
and over writing previous backups. My way I can go back in time to a specific date.

hth Jack

 

[sark666]

Thanks for the replies. Yes maybe checksum wasn't the correct term as it would always change, and yes this data changes daily, I only do reports either bi-weekly or adhoc requests, so I wasn't aware there was a problem with the data until I had a request.

Blindly backing it up (even say 7 days worth of copies) might not have avoided this.

Maybe instead of copying the contents, I'll do something a little more basic like check if the amount of entries in the database has gotten smaller or something to flag they may be an issue prior to backup. As they can't delete records so it should never get smaller. Just trying to think of something that would catch this scenario as again, blindly assuming the data is good when backing it up may not be the case, and 7 days worth of backups may not solve it. Yes I can increase the amount of backups retained (14 days, a month?) but it seems like a brute force way of ensuring there's a copy vs some type of data verification prior to backup.

 

[xenou]

I think I'd just back it up every day - if it's possible that the db can be lost and no one might notice for two weeks, then you'll have to save the daily backups for at least that stretch of time.

There's more reasons to keep backups than just the off-chance that all the tables are completely lost. It's probably best to just keep it simple - any algorithm you devise to decide whether or not to backup is likely to miss something that will leave you wishing you would have just backed it up.

In any case, a good backup plan applies to more than just this one database - all your work files are at risk if there is really no backup process in place ...

 

[sark666]

Yes I agree, I am going to backup the actual database files regardless. And yes something may slip through the cracks regardless of how I check the data. But I think I would still feel safer backing up the actual contents of the databases as well.

I just get impression from reading backup strategies that that is not a common practice.

It may be overkill, but I think I would feel better having a daily backup of the contents as well.

 

[xenou]

I think it's overkill but no harm if you go that route. I'd guess if the contents are okay, the backup of the whole file will be okay, and if there's something wrong with the contents, its a bust anyway. Usually with Access the data tables are less of a problem than gui elements such as forms/form code. Splitting the database is therefore useful to protect data (keeping the data separate from the gui elements).

ξ