Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Solved
Access backup
Posted on 2015-01-26
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
3-
3
- +2
12 Comments
Active today
Copying a file would not damage the source. Worst case is you would end up with an unusable copy.
Active 1 day ago
<<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.
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.
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)
Is it really true, though, that a .net copy isn't going to affect the Jet? (still using Jet i'm afraid)
Active 1 day ago
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
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
Active 1 day ago
<<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.
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.
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.
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.
Active 1 day ago
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
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
Active today
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.
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.
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.
...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.
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
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
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.
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.
Featured Post
With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.