Access backup

Posted on 2015-01-26
Last Modified: 2015-01-27
I know, I know, you're not supposed to copy an access file whilst its being used, could be in middle of CRUD operation, but talking real-world scenario here.
App well within mdb scale params, code very clean, small number of users pummelling away all day. Server backed up at night.
They don't want to risk having to re-enter a (potentially) whole day's data.
I don't want to sql upsize (unless I really really have to)
I've just written a .Net util for running a copy every XX mins.
I'm curious, does the .Net copy (whilst mdb in use) make a CRUD failure more likely? i.e. and put the mdb in a nasty state?
If i'm running the copy every 15 mins, even if one was bad, the one before probably won't be, but I don't want to make damaging the source mdb file more likely.
Question by:Silas2
  • 3
  • 3
  • 3
  • +2
LVL 36

Accepted Solution

PatHartman earned 250 total points
ID: 40571139
Copying a file would not damage the source.  Worst case is you would end up with an unusable copy.
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 40571144
<<I don't want to sql upsize (unless I really really have to)>>

 For a "plug and play" setup, you have to.    If you want to do something custom, then build a process in to log from within the app (much simpler to go to SQL).

<<I'm curious, does the .Net copy (whilst mdb in use) make a CRUD failure more likely? i.e. and put the mdb in a nasty state?>>

   No as it is just reading the file.

<<If i'm running the copy every 15 mins, even if one was bad, the one before probably won't be, but I don't want to make damaging the source mdb file more likely>>

 You could find however that every copy from the start of the day is inconsistent and therefore worthless, so your really no farther ahead.

Covert to SQL or build something in.


Author Comment

ID: 40571215
If the copy doesn't in any way impede the users CRUD operations, I could run the copy every 5 mins. One an hour is bound to be good!

Is it really true, though, that a .net copy isn't going to affect the Jet? (still using Jet i'm afraid)
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40571265
No, one an hour is not "bound to be good", they could all be inconsistent, and there would be no way to know which of the 12 copies, if any, is "good".

If you don't want to migrate the entire application to SQL Server, but still want to back it up regularly, it would be better to simply create the table structure in SQL server and whenever you add, delete or update a record in Access, include a SQL query that does the same to the SQL Server table, which is what I think Jim is referring to above "or build something in"

LVL 57
ID: 40571379
<<One an hour is bound to be good!>>

 As Dale said, no, and there is no way to tell if any copy you make is valid with a simple file copy.

 Backup software with a open file option would be better, but still, you might not have an accurate DB.  

 With an open file option, the file would be consistent (it pauses I/O to the file from start to finish of backing up the file), but you still might loose data if you backed up in the middle of a multi-table update.

As far as the "build something in", then could take a couple of forms:

1. Logging of every CRUD operation.
2. Writing records to archive tables.
3. Pausing the operations of all users while you make a copy of the DB.

 But in no way can you make a valid backup while users are performing operations from a file level.


Author Comment

ID: 40571565
I think I haven't emphasized the issue correctly. Its not that each CRUD operation is life-changing, its doing a whole day's entry which is.
So even if a copy is inconsistent, as long as there is no significant damage it's ok.
I'm think really regular backups would mean a wind back would be found which was ok, although , in theory , they all might be bad.
LVL 57
ID: 40571607
Well there's no telling what might be wrong with a backup done like this and simply put, your asking for trouble.

However that seems to be a answer that your un-willing to accept, so all I can say I hope it works for you.

LVL 36

Expert Comment

ID: 40571653
For a production backup, I agree with Jim, pay the money and buy a proper back up that will work with open files.

For myself, I copy the production database to my test area almost every day while the users are working.  I've never had a problem but this is for ME only so it really doesn't matter.  The users don't have access to any multi-table update features which may be why this has never caused a problem for me.  All the queries that update multiple rows or multiple tables are part of batch processes which run overnight.  The users update a single record at a time.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40571806
FWIW <No Points wanted>
...On Enterprise Backups in general.

We run MS Great Plains/SQL Server
25 users
all day long
thousands of transactions per day.
Our Backup system does a incremental backups twice per day, ...and one full overnight.
We manage medical records for 2500 individuals.
Great plains (I cant speak for other enterprise packages) functions such that if there is a crash, a transaction will get "Locked", or rolled back.
Thus the possibility of loosing  more than one translation per user, will be rare.

Not sure of the nature of your business that you need "quarter hour" backups.
If you need backups that often, then you really do not have much faith in your server(s)

Access is just one of those apps that is very sensitive to corruption, matter how often you backup.
An entire Access db can corrupt, or just parts of it.
It is very hard to find a tool that will reliably "fix" all of the corruptions that can occur.

With SQL Server you have a host of utilities to keep your DB in tip top shape.

So I say what the other experts say...
If you need this level of backup security, then you need SQL Server.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40571837
What was skipped in this discussion is that you seem to be backing up an un-split database. (a single file database)
If so, then you are on the wrong path already...
If the db was split, all you would need to backup "frequently" was the back end file.
In a split DB, a trashed user Front end could be easily replaced with no fear of losing the design or data.
With a un-split database you have to worry loosing both.

I have seem single file db get so "mangled" that running the C&R utility would actually empty out the entire database file.  Leaving (in effect)  a worthless husk of an access database.
This is just a function of how vulnerable an unsplit database can be...
This should (theoretical) never happen with a split database.

I will also share my experience with "Copying" open Access Un-split database files.
A while ago there was a question on this same topic, I decided to do some tests on my own to see if all this "you cant copy an open Access database" stuff was really valid.
I had users in the database, running things normally,
I copied the file five times during operations.
I closed the database
One of the five "copies" was corrupt, ...but the production db was fine.
So in this case the "Backup" was bad, ...not the source file I copied.

So here you not only may have to worry about the Open db corruption, ...but also the "copy/backup"


Author Comment

ID: 40572567
The app is really clean. All code in one app, data I the other. All code is 3-Tier DAL/BOL/UI, its all very clean , none of the Access nasties, bound forms, etc.
There' no transactions or replication (which would obviously leave an unstable mdb if you took a random snapshot)

I'm keeping the backend as mdb for various reasons, and my experience has been that they are uber stable if deployed correctly.

The app is in a small sorting office, so no one line is very valuable , but a whole day's data, that's serious...
However, if there's a network blip, and the mdb gets damaged to a degree which it can't be repaired, (which happened last week), then that's what I want to protect against.

I'm thinking a .Net batch routine, copying every few mins, IIIFF the copy didnt' impact the open db's performance, would be as safe as houses.

9 times out of 10 when I've copied mdb's whilst they're open the resultant file is ok, so I know theoretically there's a problem, but in practice...?
I've just never run very regular copies systematically, I wondered if there would be a performance impact, + a higher likelihood of damage.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40572625
In the end, having something as a backup is better than nothing.
You have heard all the opinions.
This decisions rests with you...

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question