How to run nightly backup of ms access 2007 database tables only
Hi, I have a database. I have seen another system in which the programmer set the db to create a backup of itself on the drive nightly. How can I set this? Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
btw,
the codes a posted at http:#a34166135 is placed in the db and called in the same db that you want to backup..
to use, you can call the function from the click event of a button
private sub cmdBackUp_Click()
dim x as integer
x = fnc_CopyFile(CurrentDb.Nam
end sub
<<the codes a posted at http:#a34166135 is placed in the db and called in the same db that you want to backup..>>
While that will work, it's not a hot idea. If the DB has any activity during the backup, you may end up with an inconsistent backup.
Only backup utlitities with a special open file option, which reach into the OS and ensures a quiet point and does a atomic move (ensures that the file is copied in it's entirety without any I/O to it occuring) will give you a consistent backup on an open file.
JimD.
While that will work, it's not a hot idea. If the DB has any activity during the backup, you may end up with an inconsistent backup.
Only backup utlitities with a special open file option, which reach into the OS and ensures a quiet point and does a atomic move (ensures that the file is copied in it's entirety without any I/O to it occuring) will give you a consistent backup on an open file.
JimD.
<If the DB has any activity during the backup, >
i don't think, someone with a sane mind will backup a db that is not idle..
i don't think, someone with a sane mind will backup a db that is not idle..
<i don't think, someone with a sane mind will backup a db that is not idle..>
The issue is that the OP did not state if the DB might be open or not.
besides, as you know, there are dozens of posts here where people try just that...
;-)
Jeff
...and as a developer, you should prevent that from happening.
LadyHagood,
<I have seen another system in which the programmer set the db to create a backup of itself on the drive nightly.>
Then can you ask that programmer what they did?
The other issue here is the state of the machine that will be doing the backup.
Can you be sure that this machine will be On?
(Not, Hibernating, Asleep or on Standby)
Suppose a night gets skipped for some reason, is this OK?
Suppose the Backup fails or is incomplete?
JeffCoachman
<I have seen another system in which the programmer set the db to create a backup of itself on the drive nightly.>
Then can you ask that programmer what they did?
The other issue here is the state of the machine that will be doing the backup.
Can you be sure that this machine will be On?
(Not, Hibernating, Asleep or on Standby)
Suppose a night gets skipped for some reason, is this OK?
Suppose the Backup fails or is incomplete?
JeffCoachman
<...and as a developer, you should prevent that from happening.>
...It is not clear if the OP is a Developer or just a person being asked to do a backup.
My point was that you can be perfectly sane and make a mistake about something you don't know.
For example what I first started driving, I was "Sane" but I did not know you had to check the oil in your engine. So I didn't and the engine blew.
Now, you could argue that no sane person would drive a car for months and never check the oil, but in this situation I did not know.
The same could apply here.
It's just that a comment like:
<i don't think, someone with a sane mind will backup a db that is not idle..>
...implies that if a person did this, that they were "not" sane...
;-)
Jeff
...It is not clear if the OP is a Developer or just a person being asked to do a backup.
My point was that you can be perfectly sane and make a mistake about something you don't know.
For example what I first started driving, I was "Sane" but I did not know you had to check the oil in your engine. So I didn't and the engine blew.
Now, you could argue that no sane person would drive a car for months and never check the oil, but in this situation I did not know.
The same could apply here.
It's just that a comment like:
<i don't think, someone with a sane mind will backup a db that is not idle..>
...implies that if a person did this, that they were "not" sane...
;-)
Jeff
"How to run nightly backup of ms access 2007 database tables only"
^^^^^^^^^^^^^^^^^^^^
LadyHagood:
Let me ask a question:
Is this "access 2007 database tables only" ... a 'back end' db that only has tables and nothing else?
If so, then I would have to ask how ... the CopyFile solution present above is going 'run itself' ... per your statement "create a backup of itself" ?
And clicking a command button to run that code would imply that ... you are 'manually' in that db to do so. And if that is the case, then yes, it will back up the db ... which eliminates the issue of
"If the DB has any activity during the backup, you may end up with an inconsistent backup." ...
because that *is* the 'activity' ... executing the code to backup the db you are in. No other activity is going to occur while that code is execution. Well ... unless there are some front ends connected to this back end ... and someone is writing data at that moment. Who knows what would happen then ?? And even if this is still a standalone db, it's not really going to back 'itself' up, short of some Timer scheme that triggers at a specific time and runs the CopyFile code. But that would imply that someone has the db open, and they could be doing some 'activity' that might would interfere with the CopyFile process.
So, this all seems a bit ... convoluted to me ...
mx
^^^^^^^^^^^^^^^^^^^^
LadyHagood:
Let me ask a question:
Is this "access 2007 database tables only" ... a 'back end' db that only has tables and nothing else?
If so, then I would have to ask how ... the CopyFile solution present above is going 'run itself' ... per your statement "create a backup of itself" ?
And clicking a command button to run that code would imply that ... you are 'manually' in that db to do so. And if that is the case, then yes, it will back up the db ... which eliminates the issue of
"If the DB has any activity during the backup, you may end up with an inconsistent backup." ...
because that *is* the 'activity' ... executing the code to backup the db you are in. No other activity is going to occur while that code is execution. Well ... unless there are some front ends connected to this back end ... and someone is writing data at that moment. Who knows what would happen then ?? And even if this is still a standalone db, it's not really going to back 'itself' up, short of some Timer scheme that triggers at a specific time and runs the CopyFile code. But that would imply that someone has the db open, and they could be doing some 'activity' that might would interfere with the CopyFile process.
So, this all seems a bit ... convoluted to me ...
mx
A JET/ACE database can't really backup itself because you can't backup a open file accurately.
What you need is another DB that has code to perform the backup (which would be a simple file copy), a batch file, or use some type of backup utility.
JimD.