Backup an open file?

[Darkbob]

I've got a file TEST.MDB and I want to back it up.

In order to back up the file I have to close the file. Problem is it's a multi-user program with perhaps 6 or 8 terminals all using the file.

I know that I have to close the application and exit the program in order to back it up.

My question is... does anybody know how to make a copy of the file when it's open and in use?

Don't say it's impossible. I know a guy who wrote a little utility to copy a file that was in use and I saw it run. He's long gone and I never did see the source code... but there IS a way. Anybody know how you do it?

Many thanks.

--DB

[si_the_geek]

It is only safe to copy an in-use file if it is not being written to - and an Access database is very unlikely to be in that state, even if records are not being edited/added.

As it is an Access database, there is no safe/reliable way to do what you want I'm afraid.

[Darkbob]

Hey SI... Check this out and tell me where I'm going wrong.

It IS possible to make a copy of the database file even when an open connection exists. I found the CopyFileA system call and it doesn't seem to be bothered by the fact that the file is open.

On the other hand, my program DOES seem to be bothered when it tries to write to the database while it's being backed up. The program just locks right up.

But if the program is just sitting there with the databases open but not in use the CopyFileA function doesn't seem to cause any problems and the copy just works fine.

Sooo... I came up with a fairly simple solution. At the start of the backup my program writes a file called "PauseMe.txt" out to a shared folder then I wait 15 seconds.

While my backup program is waiting, the other terminals are running a program with a timer on the main page set to check every 10 seconds for the PauseMe.txt file. When that file is detected, the program throws up a red screen that asks the user to pause. For good measure I did a DBEngine.Idle dbRefreshCache which is supposed to write out all records and a DoEvents.

Worst case there should now be a 5 second break while nothing happens at all.

So on the 16th second the backup application runs through the database files and makes a local copy of them using the CopyFileA function.

After that's done the backup routine deletes the PauseMe.txt. The red screen goes away on the terminals and everyone gets back to work.

Meanwhile the backup routine can now write the local copy of the files to a CD or DVD or memory stick or whatever.

It seems to me that in theory it should work but I'll test it on a 5 terminal network over the next week and see what happens.

Bob

[si_the_geek]

I wouldn't trust that method at all.. if there is any kind of connection open, there is the chance that something is being written to the file (even if you haven't written any data recently), and if that happens the backup will be corrupt. It might not happen in your tests (or for a while after), but at some point your backup will be useless.

What I would do instead is close the connections. As they seem to be inside Access itself, have the clients switch to a special 'waiting' database, and change back when the work is done - from the backup process you can check the .ldb file to see if there are any connections still open.

An alternative would be to use a 'proper' database to actually hold the data (such as SQL Server Express), as with those you can safely do a hot backup as you want.

[Darkbob]

Thanks SI. I appreciate your advice.

--DB