1

Disclaimer: I'm a software engineer, not an IT professional, sorry if some of my suggestions are silly or otherwise ridiculous.

A windows application, which will remaining running at all times, is connected to, and constantly interacting with, several MS Access databases. The application may be reading or writing to any of these databases at any time. Some of the databases are small, and stay roughly the same size. Some of the databases are (relatively) large (a few hundred thousand rows), and are appended to constantly. We'd like to make a backup of this set of databases every X minutes without shutting down the application. The backup will either be on localhost (2nd hard drive) or on the (100mbit) local network.

The application will be running on either Windows XP or Windows 7.

I do realize the real solution is "stop using MS Access", which isn't currently an option.

Option 1: Copy files

We could just copy the files, using the windows copy (or xcopy?) command, or some (preferably free) 3rd party file copier. Is there any problem with this?

A downside to this option is the larger databases are entirely copied every time, which could take more than a few seconds. What happens when an MS Access database is being written to while it is being copied? (Data corruption? Failure to copy?)

Option 2: Synchronize

We could have some 3rd party process running which keeps the backup MS Access databases in sync with their live counterparts. Many existing programs I looked at support "real" database engines like SQL Server, Postgres, etc, but it seems MS Access is lacking support here (or maybe I'm just not searching for the right keywords).

Option 3?

epalm
  • 143
  • 2
  • 5
  • 4
    Your organization has an unrealistic expectation of 100% uptime for this system. You need to explain to them that they can have scheduled downtime or unplanned/disastrous outages, their choice, but that you would recommend the scheduled approach. Migrating to a real database is the *only* way to create options for actual high availability, and your business decision makers need to know that. – Skyhawk Apr 19 '12 at 18:16

2 Answers2

5

Any time you have a file open you run the risk of having a corrupted copy when you just copy it to another location. As you alluded, Access isn't really meant as a multiuser database and I don't know of utilities that will safely dump the contents to another file the way you can with most SQL databases.

You might have some luck using volume shadow copies. Getting a real backup program for Windows should allow you to do this since it works at the block level; windows' built-in backup utility may do it for you. This would get around the file locking issue.

If the Windows Backup solution doesn't work you may have to have a maintenance window where you do just close out all the remote Access connections and create a simple copy to another location.

Your best bet would be to get a backup program that supports making backups despite file locks on NTFS though. It will save you management headaches in the future and avoid duct-tape solutions.

And to my knowledge there is no way to cluster or mirror an Access database. It simply wasn't meant for that, and when an organization reaches that level, they generally use a true database engine that can support backups via dump, or clustering, or mirroring, so there are other installations from which to make "live" backups.

Bart Silverstrim
  • 31,092
  • 9
  • 65
  • 87
2

Option 3: BUY THE RIGHT TOOL!

You are using Access for something it was never designed for -- High-uptime, high-concurrency, high-volume data storage.

Access is a TOY - A pretender to the database throne which is suited for very small projects which aren't business-critical.
The use case you describe is what SQL Server was made for. Spend the money and get what you need: A database that can handle high-volume, high-concurrency utilization, with replication and/or backups that can be run without substantially affecting your users.

voretaq7
  • 79,345
  • 17
  • 128
  • 213
  • I agree. Quote, "I do realize the real solution is "stop using MS Access", which isn't currently an option." – epalm Apr 19 '12 at 19:13
  • @epalm I'm aware of your constraint as stated, but it not being an immediate option doesn't change my answer -- I try to only recommend the *right* solution, and I try to never recommend solutions that can turn around and bite people in the behind later (which is all I can really offer you if you can't migrate off of Access). – voretaq7 Apr 19 '12 at 19:58
  • I understand. But your answer, while the most correct, is also the most useless to me. (Again, believe me, no one wants to ditch access more than I do!) – epalm Apr 19 '12 at 21:00
  • 1
    It's comforting though, that we have the same opinion of MS Access. I sat in our boardroom and prefaced the meeting with "no matter what, there probably won't be a robust solution to this problem". As is often the case in software (and IT, I'd imagine), I'm looking for the least worst option :) – epalm Apr 19 '12 at 21:05
  • @epalm You can always point your higher-ups at my answer -- maybe it will help them see reason :-D – voretaq7 Apr 19 '12 at 21:44