Go Premium for a chance to win a PS4. Enter to Win


Access backup

Posted on 2015-01-26
Medium Priority
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 40

Accepted Solution

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

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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)
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 49

Expert Comment

by:Dale Fye
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 58
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 58
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 40

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, ...no 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, ...so 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 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