Backup of open database - how do I do this from code. 2

[Gzep]

Hi.

I am trying to backup a database that is open, but at a time where there should be no activity.

The FileCopy function returns an access denied error (as does shell(copy...) function), as the database is open.

Is anyone aware of a simple way of backing up an access db while it is open? (and the current application db is connected to it - and maybe others also)

I can use windows explorer to manually copy the file, with success, but I need to be able to do it from code.

I had a look at the general backup thread, and normal backup software just skips open database files - and never backs them up!!!

Gzep.

 

[Garridon]

There's an FAQ on the General Access forum that covers backing up for a database with a backend.

Linda Adams (Garridon@aol.com)
"The Importance of Being Grammarian," published in The Toastmaster, March 2001

http://www.geocities.com/hallona_2000

http://www.david-hedison.com

 

[MichaelRed]

Well,

It is a BAD idea. Windows doesn't allow the copy of an open file FOR GOOD REASONS. If a file copy operation is applied to an open file, the file content can change during the copy. In the Windows O.S.'s the file structure (i.e. dics blocks) may change as a result of the change. Whie this occurs, the COPY will be corrupt. You NOW have a useless piece of file occupying space on the target storage media.

DO whatever is necessary to LOG OFF all users and use the filecopy method from another program. If you need to, do this (force all users to LOG OFF) through code. THEN do the filecopy function.

Understand. Copying a corrupt db is worse than not copying it at all. Having the possibility of a corrupt copy of the database as your back up should be grounds for nighmares and worse.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[LonnieJohnson]

Great answer Michael. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[jimmythegeek]

Michael,

I used to agree with your statment until I was forced by my current employer to back up a production db every 10 minutes. Obviously the FileCopy method was not an option.

So in my investigation and testing, I found that using the FileSystemObject actually works very well.

First, using the FileSystemObject.CopyFile method will copy an open database. Second, it will not copy any pages (Access' locking method) that are currently locked.

I was concerned as you stated about corruption, but we have had this running for over a month, backing up the mdb every 10 minutes, 24 hours a day, on a significant production db with no problems.

For what its worth, below is the code:

==========
Public Sub BackUpFile()
Dim oFiles As FileSystemObject
Dim strSource As String, strDest As String

strSource = "U:\My Documents\Databases\Resource.mdb"
strDest = "C:\Temp\"

Set oFiles = New FileSystemObject

oFiles.CopyFile strSource, strDest

Set oFiles = Nothing
End Sub
========== Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[MichaelRed]

Jim,

I strongly suggest that you taks a serious look at the backups. Select several and place them in isolation and open. Compare the table type record sets. Especially if they are part of the db. Go through some operations. If - after these investigations, you feel there is no problem, I will be suprised. I have done similar operations (no where as frequently!) and always have found problems with the 'backups'.

The ONLY way I know to come close is to use the db functions to "Update" the base tables on the "backup" db with the contents of the production db. This is relatively straighforward, as you can create a blank db in code and then vreate the tables simply by doing a make table query for each table (omitting the system tables) in hte current db. It is not entirely satisfactoy for me, but it is " ... close enough for government work ... ".

I can not imagine doing this on a timer basis set up for 10 minutes, as it would provide a huge performance penalty for all other operations. I, personally, would probably fight the 50 or so time per day back up quite a ways up the ladder, but them that is almost certainly a factor in my current (Un) employment status.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Gzep]

G'day.

Thanks for all the replys.
1. Garridon: can't find the FAQ. And yeah, I should have looked there first, probably not done in code anyway...

2. MichaelRed: First line of request - "at a time where where should be no activity" (of course it's a bad idea, that why they asked me to do it. (i did something impossible once before, and they kinda expect it again)

3. LonnieJohnson: Ta. :-(

4. jimmythegeek: Thanks. much closer to what i was after, but i can't find the scripting runtime VBENLR.chm helpfile, which makes it hard to research this function. Since Scripting is designed for shared internet stuff, maybe the functionality to copy whole valid files is already there!

5. MichaelRed: That was my concern too. I can't afford the overhead of setting the system up for replication either.

After reading your anwers, i conclude that I was really looking for something that checks the access record-locking table, and copies the valid records with some sort of retry on the locked ones, or better yet grabs the old values of the locked ones, to give a complete valid set, albeit not 100% up to date(which is fine). Looks like I get to write some more esoteric code.

Gzep

 

[MichaelRed]

Gzep,

If you can accept a less than 100% current set of records and can ALSO accept backing ONLY the TABLES, the process outlined in my lasst post is O.K. It doesn't take much (any?) longer than the file backup and can be somewhat smaller. It has the distinct advantage of (almost) never leaving you with corrupt data. It IS a good idea to run such a process during periods of inactivity. If I had to do it all over again, I would probably do it from a db constructed specifically for the purpose. Set it up to execute from the startup object and have the db called from a system timer type of process. I would also enhance it somewhat, checking for number of users and / or number of pages locked and skip to backup if either were above some threshold. Should also log the activity in a table after the backup.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Gzep]

Hi again.

Thanks MichaelRed.

jimmythegeek's method has fixed one application, but for the other, it looks like I'll have to copy the tables one-at-a-time. I'll probably use the ExportDatabase Function, and push them out that way.

Gzep.