Solved

Access backup

Posted on 2015-01-26
12
95 Views
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.
0
Comment
Question by:Silas2
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 34

Accepted Solution

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

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
<<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.

 Jim.
0
 

Author Comment

by:Silas2
Comment Utility
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)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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"

Dale
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Jim.
0
 

Author Comment

by:Silas2
Comment Utility
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.

Jim
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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"

JeffCoachman
0
 

Author Comment

by:Silas2
Comment Utility
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.
0
 
LVL 74

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now