By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,384 Members | 714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,384 IT Pros & Developers. It's quick & easy.

Proper method for creating nightly backups of the back-end file

P: n/a
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 9 '06 #1
Share this Question
Share on Google+
18 Replies


P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote
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
Aug 9 '06 #2

P: n/a
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 :) ].

Larry Linson wrote:
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 9 '06 #3

P: n/a
Ron
"Larry Linson" <bo*****@localhost.notwrote in message
news:intCg.8797$7m5.5740@trnddc05...
"rdemyan via AccessMonster.com" <u6836@uwewrote
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).
<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
Aug 10 '06 #4

P: n/a
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.

Ron wrote:
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 10 '06 #5

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:6487a1e1ca930@uwe:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 10 '06 #6

P: n/a
"Larry Linson" <bo*****@localhost.notwrote in
news:intCg.8797$7m5.5740@trnddc05:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 10 '06 #7

P: n/a
Br

Ron wrote:
"Larry Linson" <bo*****@localhost.notwrote in message
news:intCg.8797$7m5.5740@trnddc05...
"rdemyan via AccessMonster.com" <u6836@uwewrote
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).
<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
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

Aug 10 '06 #8

P: n/a
Yes, I was wondering about this possibility.

Can you point me to some code?

Thanks.

David W. Fenton wrote:
>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.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 10 '06 #9

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:648b8648d5987@uwe:
David W. Fenton wrote:
>>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.

Yes, I was wondering about this possibility.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #10

P: n/a
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.

David W. Fenton wrote:
>>>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.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 11 '06 #11

P: n/a
David W. Fenton wrote:
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!

Aug 11 '06 #12

P: n/a
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.

Lyle Fairfield wrote:
>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!
--
Message posted via http://www.accessmonster.com

Aug 11 '06 #13

P: n/a
rdemyan via AccessMonster.com wrote:
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!

Aug 11 '06 #14

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:64982ef88897c@uwe:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #15

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:64982ef88897c@uwe:
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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #16

P: n/a

"rdemyan via AccessMonster.com" <u6836@uweschreef in bericht news:6487a1e1ca930@uwe...
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.
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
Aug 12 '06 #17

P: n/a
rdemyan via AccessMonster.com wrote:
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.

Aug 12 '06 #18

P: n/a
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.

Lyle Fairfield wrote:
>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.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 16 '06 #19

This discussion thread is closed

Replies have been disabled for this discussion.