 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / August 2006Proper method for creating nightly backups of the back-end file
Here's my plan for creating nightly backups of the production back-end file (the IT staff in their infinite wisdom have prevented use of Windows Scheduler and users do not have administrative rights to Windows). Candace Tripp has an automatic backing up program that I modified for our use. You can schedule a time for the backup to occur. Since Windows task scheduler is not available to us, this means that the auto backup program will have to remain open. Now this auto backup program will reside on the local PC. A back-end file on the server will be specified to be backed up to a folder called 'Backups' also on the server. I think I read somewhere that it is not a good idea to backup back-end files from one location to another (i.e. server to PC or one server to another). My plan seems simple and reasonable to me but am I missing anything that I should be aware of. Thanks. > I think I read somewhere that it is not a > good idea to backup back-end files from > one location to another (i.e. server to PC or one > server to another). Do you remember reading "why" it would not be a good idea? It's only my opinion, but influenced by multiple-average-programmer-lifetimes in the business, that should that "backup folder" happen to be on the same physical drive, and that physical drive were to go belly-up, it would have been A Very Good Idea Indeed to have backed up to a different locale. A backup of an Access database is a simple matter of getting everyone logged off, which is not a terribly difficult thing to do, and doing a file copy (or a Compact and Repair). > My plan seems simple and reasonable to me but > am I missing anything that I should be aware of. See the above. I can't comment on Candace Tripp's "automatic backing up program," as I am not familiar with it. Are you really serious that the IT department will not work with a user department to assure that vital business information is backed up? I've had to deal with some pretty hard-@@@ IT departments in my time, but none who wanted an escalation up through the user's chain of command to the top level, and then have that descend through their chain of command that they were "putting vital business data at risk." And, the usual, "we take a disk image every morning at X AM" doesn't work all the time for Access/Jet databases, unless you assure that no one is logged in to your database when the disk image is made. Larry Linson Microsoft Access MVP Candace's program is very nice and convenient. Code was relatively easy to understand even for this novice, so I was able to customize to our needs. Yeah, I was wondering about backing up to the same server. But I'm sure I saw discussions on the pitfalls of using FileCopy across a network connection [admittedly this was at least 6 months ago and I've recently added yet another year on to my calendar :) ]. > > I think I read somewhere that it is not a > > good idea to backup back-end files from [quoted text clipped - 33 lines] > Larry Linson > Microsoft Access MVP > > I think I read somewhere that it is not a > > good idea to backup back-end files from [quoted text clipped - 12 lines] > logged off, which is not a terribly difficult thing to do, and doing a > file copy (or a Compact and Repair). <snip> Hi Larry. Can the database be backed up even though everyone's not "logged off". I've got a fairly simple database (about 12 tables on the back end) and I'm having the user do an xcopy from main computer to another, then that 2nd computer is backed up to tape every so often. I've never had the user run into an error message when copying the database to the 2nd computer, and they do that twice a day regardless if some other computer may have that back end open or not. Although the other users may leave their computer in the database, the backup from computer to computer is done at a time when no one would normally be inputting data. I've taken that file a couple times (the backup) and put it on my system and just checked around, and they've never been missing anything (that I can see). Am I asking for trouble here? Thanks ron Ron: In some of the research I've been doing, it appears that most agree that when you make a copy while a backend is in use, there is the potential for corruption. Like you, I personally have not experienced this. The general consensus seems to be that it is the copy that will be corrupted and not the main backend (but I don't know if this is guaranteed). Hopefully, some others will respond. >> > I think I read somewhere that it is not a >> > good idea to backup back-end files from [quoted text clipped - 21 lines] >Thanks >ron > > > I think I read somewhere that it is not a > > > good idea to backup back-end files from [quoted text clipped - 32 lines] > Thanks > ron If no-one is using data then they may not have an open connection to the database. There are a few ways to make sure everyone is logged off. You can write code to list all the open connections to the backend, if connections are found report it (email, log table, or whatever saying backup failed). You can also put a flag in the backend that will get checked by every front-end every X minutes. If the flag is set each front end will then trigger a log out/shut down automatically. regards, Br@dley > A backup of an Access database is a simple matter of getting > everyone logged off, which is not a terribly difficult thing to > do, and doing a file copy (or a Compact and Repair). You could get a live backup except for the latest unsaved updates to records currently being edited, by copying the data in code.  SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ Yes, I was wondering about this possibility. Can you point me to some code? Thanks. >> A backup of an Access database is a simple matter of getting >> everyone logged off, which is not a terribly difficult thing to >> do, and doing a file copy (or a Compact and Repair). > >You could get a live backup except for the latest unsaved updates to >records currently being edited, by copying the data in code. >>> A backup of an Access database is a simple matter of getting >>> everyone logged off, which is not a terribly difficult thing to [quoted text clipped - 6 lines] > > Can you point me to some code? I don't have any, but it oughtn't be too hard to put together. Once you've written it for one table, you could easily repurpose the code to process a batch of tables. You could copy the table structures, then append the data, or use a template MDB with the empty table structures and then append the data to a copy of the template MDB. I'd certainly do the latter myself, as the code needed to fully replicate all the properties in an Access table is actually quite complex. There's also Lyle's old SaveAsText method: objAccess.Application.SaveAsText 6, vbNullString, "MyDatabase.mdb" but I've never figured out a way to run it from the front end to back up a back end. If you were satisfied with running it in the back end, it would be pretty easy to set up a startup form to capture a startup command and run this code if you open the database with the appropriate commandline switch. Look at the Command function in Access help for a starting point. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ David: What happens if I'm running this "append table data" code and the code encounters a table that is currently being accessed either with simple SELECT statements or perhaps being written to by one of the users. My assumption is that such code would most likely be run by a user when other users are still logged on. Thanks. >>>> A backup of an Access database is a simple matter of getting >>>> everyone logged off, which is not a terribly difficult thing to [quoted text clipped - 21 lines] >with the appropriate commandline switch. Look at the Command >function in Access help for a starting point. > What happens if I'm running this "append table data" code and the > code encounters a table that is currently being accessed either > with simple SELECT statements or perhaps being written to by one > of the users. My assumption is that such code would most likely > be run by a user when other users are still logged on. A select shouldn't be a problem, as selects are read-only and won't put a read lock on the table (which is all you need to copy the data). If there's a write lock, I don't know what happens. If it's a pessimistic lock, I guess you'd not be able to read? Or not be able to write? I'm not certain. I think what you'd get with a write lock is just the current data in the table. It might be invalid 1 second after you copied the data, but it would still be consistent at the time it was created. Though I guess now that I think about it, you'd want to be appending to tables without RI enforced, since otherwise, you'd possibly get a situation where the child records existed at the time you copied the child table, but the parent didn't at the time you copied the parent table. Or vice versa (not so much of a problem). I'm not sure the chance of this happening is sufficiently large to warrant worrying about it. And maybe the SaveAsText solution gets around all of it in some fashion. Have you tried it? Shouldn't be hard to answer your questions about the original scenario or about the SaveAsText option. If you experiment with either, I'd be very appreciative if you'd report back. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > What happens if I'm running this "append table data" code and the > code encounters a table that is currently being accessed either > with simple SELECT statements or perhaps being written to by one > of the users. My assumption is that such code would most likely > be run by a user when other users are still logged on. One addendum to my previous post. I said that I didn't necessarily think that the inconsistencies from read/write locks could happen often enough to justify worrying about it. That might imply that I'd think the same about a file system copy, but there's a big difference: The file system copy doesn't care if the data it's reading off the disk is internally consistent from *Jet's* point of view. All it cares about is that the data is internally consistent from the file system's point of view. If you're copying via Jet interfaces, as you would be with table appends, then you're only going to get data on the other end that is internally consistent by Jet standards, though it may have inconsistencies from the standpoint of your business rules. A file copy can be perfectly valid from the file system's point of view, but internally corrupt -- every corrupt MDB meets those criteria. But no data that is successfully read and written via Jet/DAO interfaces can ever be corrupt from Jet's point of view. There may be limitations as to what you *can* get through that interface, but they do not involve any danger of creating a copy that is corrupt from Jet's point of view, as the file system copy risks. Does that make sense as a coherent position? SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > objAccess.Application.SaveAsText 6, vbNullString, "MyDatabase.mdb" > > but I've never figured out a way to run it from the front end to > back up a back end. If you were satisfied with running it in the > back end, it would be pretty easy to set up a startup form ... Sure. In the backend I have: Public Function BackUpThisDB() Dim n(0 To 1) As String n(0) = Replace(CurrentProject.FullName, ".", Format(Now(), "yyyymmdd\*") & ".") n(1) = Replace(CurrentProject.FullName, ".", Format(Now(), "yyyymmddhhnnss") & ".") If Len(Dir$(n(0))) = 0 Then SaveAsText 6, "", n(1) End If End Function This checks for a backup having been made today, and if not, makes one. I call it with the AutoExec macro. (Beginners who try such things often decide in their "wisdom" to make the procedure a sub. A Macro cannot call a sub. Then they post back, "Followed instructions exactly and ... Didn't work!" Uh Huh! The action of the macro is RunCode and the Argument is BackUpThisDB (). So when the backend is opened it's backed up. I could point out, again not for you but for beginners, the backend is not opened when we link to its tables, or use its tables. That's our problem with backing it up this way in the first place, it's Not Open. So the person maintaining the db would only have to open it each day and then close it. Of course, if windows scheduler is allowed we can just run something that opens and closes the backend every midnight or whatever. I haven't touched permissions, assuming that he/she who manages the setup has enough for anything. I'm certainly not recommending this for the OP. I think he is happy only with something inefficient, clumsy and stupid, preferably with several thousand lines of MS KB drivel. Then he can think, "3000 lines (not one of which I understand), boy, now I'm REALLY a programmer." Uh HUH! Nice compact code. I'd like to actually run it from the front end. I assume I can just replace CurrentProject.FullName with the complete path to the backend (which I conveniently store on my hidden startup form)? Also, do you know how this code responds if a backend is in use while it is being run? Stupid question I'm sure, but how does one, in code, restore the text file? Thanks. >> objAccess.Application.SaveAsText 6, vbNullString, "MyDatabase.mdb" >> [quoted text clipped - 45 lines] >(not one of which I understand), boy, now I'm REALLY a programmer." Uh >HUH! > Nice compact code. > > I'd like to actually run it from the front end. Great! Let us know how it works and be sure to post the code! > Also, do you know how this code responds if a backend is in use while it is > being run? It''s run from the backend. So the backend is always in use when it is being run. This is because you are using it. After you complete your code to run it from the front end, you will be able to tell us how it works while someone is linked to it. Of course, we are waiting breathelessly for that but my face is getting blue now. > Stupid question I'm sure, but how does one, in code, restore the text file? It's not a text file; it's an Access file, mdb or adp, so the answer is, do nothing. I'm posting for Lyle's benefit as I realize he is waiting on pin and needles :) Seriously, the code that Lyle posted does work nicely, except for one thing. My backends are workgroup secured. Using Lyle's code produces a copy of the backend file that is no longer workgroup secured! This does not fit in with my overall design. I did a cursory search through some posts and was not able to find anything on creating workgroup security in code. Therefore, I am abondoning this approach. >> Also, do you know how this code responds if a backend is in use while it is >> being run? [quoted text clipped - 10 lines] >It's not a text file; it's an Access file, mdb or adp, so the answer >is, do nothing. > I think I read somewhere that it is not a good idea to > backup back-end files from one location to another (i.e. server to > PC or one server to another). Perhaps replication was involved? That's the only situation where I can think that this would be a problem. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Here's my plan for creating nightly backups of the production back-end file > (the IT staff in their infinite wisdom have prevented use of Windows [quoted text clipped - 15 lines] > > Thanks. Just 'jumping in' in this thread... I do the following: Code in the FE makes a backup of the BE when it has not been done allready the same day. So when the backup is made succesfully a value LastBackupDate is updated (in a textfile created in the BE-directory) So only when the *first user* logs in (LastBackupDate <> Date) the backup-code is executed. When the second or third or xx user logs in (LastBackupDate=Date) nothing happens. Copying the file is simply done with code like FileCopy strBEPath, strToDaysBackupName strBEPath is the full path to the current backend. Works perfectly for me. Arno R |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.