Backup Copy of Database

[AmyLynnHill]

Greetings,
I have a database which is heavily relied on to provide information running a business. Their are 20-30 people in it at a time inputting information, creating documents ect...

Our server has gone down twice within the last week and information had to be restored from out IT dept, back two days. We lost a day of production. Not good.

So. My boss has given me the challenge to have an automated "backup" copy created every 4 hours and saved to a different server.

After researching option. This is what I came up with. Create a database on my c: that links to the critical tables in the database. Find code that will copy the linked database every 4 hours, compact and repair also.

The stumbling blocks I'm running into is finding something that will allow you to save the file while there are users in the database. And, being able to restore information quickly.

Anyone have suggestion on my approach? Code? Examples?

Welcome to any suggestions.......

 

[boblarson]

Is this database SPLIT (frontend/backend) with the backend on the server and a COPY of the frontend on EACH USER'S machine? If not, it needs to be in order to avoid corruption issues. See here for more about splitting.

 

[AmyLynnHill]

no it is not split.

Any suggestion with the way currently set up? not split?

 

[AmyLynnHill]

I appeciate your information regarding splitting the database and understand the concept. I'm just not sure I'll be able to buy-in from the people running the show to allow that kind of time to correcting the set-up.

 

[boblarson]

no it is not split.

Any suggestion with the way currently set up? not split?

If you don't split it and run it the way I said, you can expect to have corruption happen frequently. That is simply the nature of an Access file running with multiple users over a network.

And, no you can't make backups with people logged in.

So, Split the database and give each user a copy of the frontend. If you don't want to send out a new frontend each time the frontend is modified then you can use my free Frontend Auto Update Enabling tool (see my signature) which enables your front end to:

1. When the user opens their frontend, it checks to see if it is the latest version.

2. If not the latest version then it tells the user that the frontend is out of date and it closes.

3. It then deletes the old copy off their machine and copies down from the network location a new copy of the frontend.

4. and then reopens automatically for the user.


Also, having a split database and separate copies of frontends makes working on the frontend a breeze because nobody is in the master copy, so you don't end up having to have everyone out of the database like you would if you don't do it.

 

[shadow9449]

no it is not split.

Any suggestion with the way currently set up? not split?

It actually would be somewhat less coding to do it without splitting but you are really likely to have a LOT of problems with 20 people in an unsplit database. It would be wise to split it and then do the backup scripts in a proper environment before you have a real mess on your hands. As you'll see in Albert Kallal's article, Access really doesn't like when people share forms and other objects.

I'll give you one example:

You have a client form. Both Joe and Tracy (users) are accessing the same client. Joe inputs some data and before he closes the screen or goes to another client, Tracy accesses the same record. What Access is SUPPOSED to do (depending on what record locking you've applied) is to give Tracy the option to save or discard. What Access DOES (if you haven's split) is give you that option and then destroy the whole client record and possibly more.

It's very important that you are trying to implement a good backup scheme but taking the precaution of not allowing people to destroy the data is very important too. The more users, the more likely to encounter this and other problems.

SHADOW

 

[MSAccessRookie]

I appeciate your information regarding splitting the database and understand the concept. I'm just not sure I'll be able to buy-in from the people running the show to allow that kind of time to correcting the set-up.

The "Time Required" is actually very little. Using the Built In Database Splitting Tool, It should not take more than a few minutes to split the database, and probably not more than an hour to validate the new database structure. Remember that to validate the setup, you will need to place the Front End on the desktop of a test user (perhaps yourself), and the Back End on a Server that all users will have access to (perhaps the same one that it is on now).

Once the Database has been validated, there should be a one time cost of a few minutes per workstation to move a copy of the Front End into place and verify that it works.

As you can see, the Front End Cost (One time) is not as dramatic as you might have thought, and perhaps you can justify it to the management after all.

 

[AmyLynnHill]

Good Enough!

question..how does this solve my problem of server going down and data lost. Is it due to the BE being on a seperate server?

 

[boblarson]

I'm just not sure I'll be able to buy-in from the people running the show to allow that kind of time to correcting the set-up.

How much time and money are you losing by having it go corrupt every so often? You'll save a ton of time/money by splitting and doing what I said with the frontends.

Also, one thing I hadn't mentioned, is that in your current state - if it goes down, all cannot use it. If you have it split and one user's frontend corrupts (not likely but still it is Access so possible) then only THEY are down (until you get them a new copy of the frontend) and the others can still be working away as if nothing ever happened.

 

[MSAccessRookie]

Good Enough!

question..how does this solve my problem of server going down and data lost. Is it due to the BE being on a seperate server?

It may not solve the problem of the server going down (I am sorry to say that the problem of server failure will remain for your SA Group to identify and resolve), but it should be a giant step toward solving the problem of data corruption.

 

[boblarson]

Good Enough!

question..how does this solve my problem of server going down and data lost. Is it due to the BE being on a seperate server?

the problem now is that a single file, if someone is connected and the server goes down - Corruption.

If you have it split and FE copies on each workstation, if the server goes down - nothing lost. You just wait until the server is back. Possibly a user might have a corrupt FE but easily given a new copy.

 

[AmyLynnHill]

Thanks for all you posts/help. much to discuss and think about. I appeciate your help!

 

[MSAccessRookie]

Thanks for all you posts/help. much to discuss and think about. I appeciate your help!

Good luck and post back if you need any further direction.

 

[AmyLynnHill]

Will do, most likely will have more questions once I dig into it....Thanks again.

 

[shadow9449]

I just want to add one thing:

I've used Albert Kallal's zip solution with databases that are running:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm

The trick will be to have a user's open front end reference the back end and zip that and move it to the backup location. It can be done and it's probably your best bet.

SHADOW

 

[boblarson]

It can be done and it's probably your best bet.

Not the BEST bet. It may make a backup of the backend, but it still needs to be split otherwise the corruption will keep occuring and that does not have to happen. And, if split, and normal backups are being done on the server, it is not likely necessary to do extra backups on the backend (unless they do want to do so because of a high rate of data entry). Regardless, if it isn't split, you can't make a backup copy of the database while people are in it.

 

[shadow9449]

Hi Bob

In post #6 I pointed out that splitting the database is the first priority, and then backing up the back end is the second priority (seeing that her goal is to back up the data). I then said in post #15 that the back end can be backed up using Mr. Kallal's technique by using a script in the user's open front end.

So, I'm agreeing with you that this database must be split. I hope this clarifies.

SHADOW

 

[boblarson]

So, I'm agreeing with you that this database must be split. I hope this clarifies.

Sorry, guess that didn't sink in

 

[shadow9449]

Sorry, guess that didn't sink in

No problem. If it wasn't sufficiently clear to you then it may not have been clear to someone else so it's worth restating.

SHADOW