Automating database backup (1 Viewer)

tmyers

Well-known member
Local time
Today, 08:37
Joined
Sep 8, 2020
Messages
1,090
I have seen a couple different post/articles on how to do this, but before I just went and paste various code into Access, I wanted to get others opinions on it.
I was reading the following:


It seems like any one of those would work, as all I am wanting to do, is say approximately every hour, the database create a back up version of itself. Ideally, it would save an all new copy of itself with a time stamp. I could then keep however many backups I would want to, or at some point add in where the code would auto-delete all files that are say 5+ days old.

But out of those, which would be the better one to use?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
Hi. I haven't read any of the links you posted, but are you trying to create a backup of the FE or the BE. If the BE, take a look at the sample code I posted a while ago at:

 

Gasman

Enthusiastic Amateur
Local time
Today, 12:37
Joined
Sep 21, 2011
Messages
14,038
Here is what I have used, manually though. The functions are because I used the Access Switchboard form.
There is more there than you need for backup, but it is easier to just upload the whole module.

HTH
 

Attachments

  • DB Functions.txt
    4.5 KB · Views: 579
Last edited:

Isaac

Lifelong Learner
Local time
Today, 05:37
Joined
Mar 14, 2017
Messages
8,738
are you wanting to create a backup version of the file that is being used? the front end? or a separate back end file?

generally speaking, if the answer is the former, that shouldn't be necessary really, because everyone is using their own local copy of the front end, and you don't care if it gets corrupted (etc), they just reinstall by getting a new copy of the master.

but i'll throw this out there just for fun.....recently I was asked to create a backup file of an Excel file - the same one being used. Prior to realizing there was such a thing as SaveCopyAs, I came up with following method which worked a charm and backed up the file every time it was closed:

1) VBScript to copy current file to backed up filename
2) in that VBScript, there was a 10 second wait
3) Shell out the VBScript using vba in the Excel file in its Close event

Technically, you could do something like this with Access too not any different really.

If you are just trying to back up the back end, I would probably create something to be called by windows task scheduler on a server if possible.
Or just double check with your I.T. dept...........I can't tell you how many places I've worked where the people had literally no idea that server backups, available/accessible in the form of Folder Properties>Previous Versions, was already turned on...and there was virtually no need to perform more backups.
 

bastanu

AWF VIP
Local time
Today, 05:37
Joined
Apr 13, 2010
Messages
1,401
I've been using this utility in conjuction with Windows Scheduler to automate\compact Access back-ends:
It does the compacting on the local C:\ dirve as doing on the network can lead to unusable files if the network connection fails during the compacting.
Cheers,
Vlad
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:37
Joined
May 7, 2009
Messages
19,169
download and use Goodsync.
it is a Set and Forget backup program.
it also has incremental backup.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 19, 2002
Messages
42,970
If the BE and the master copy of the FE are on a server as they should be, the server should already be getting backed up regularly. You can check with your IT department to find out the frequency. If you think it is insufficient, then look into the utilities at www.FMSINC.com

If you are talking about frequent backups of the FE while you are developing, I can help with some code for that. I do it two ways, one copies the FE to both a local and a server directory and the other exports all objects to text and you can set that up to be wherever you want. In order to not annoy the users, the login form which is the first to open and it stays open throughout the secession and is the last to close only asks "me" if I want to back up or it does it automatically if it is me. This is hardcoded but you can create a table and put the userid in the table so that you can have multiples. That is helpful if you work on multiple computers and have different IDs on each or multiple people might make changes and you want to backup if any developer makes a change.

Let us know why you want the back up and we can give more specific advice.
 

tmyers

Well-known member
Local time
Today, 08:37
Joined
Sep 8, 2020
Messages
1,090
This would be for the Backend file. It is on our local server, which is backed up nightly. The reason I would like to go a step further would be due to the fact our building is known to drop power randomly several times a year. Depending on the day, a lot of work could potentially be lost if during one of those events, the backend gets corrupted. Granted I don't know the likelihood of such an event since the server has a UPS and such on it. So while the front ends would cut off, the backend would keep chugging along until the server safely shuts down.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 19, 2002
Messages
42,970
Then I suggest the backup utility sold by FMSINC.com
 

hfsitumo2001

Member
Local time
Today, 05:37
Joined
Jan 17, 2021
Messages
365
are you wanting to create a backup version of the file that is being used? the front end? or a separate back end file?

generally speaking, if the answer is the former, that shouldn't be necessary really, because everyone is using their own local copy of the front end, and you don't care if it gets corrupted (etc), they just reinstall by getting a new copy of the master.

but i'll throw this out there just for fun.....recently I was asked to create a backup file of an Excel file - the same one being used. Prior to realizing there was such a thing as SaveCopyAs, I came up with following method which worked a charm and backed up the file every time it was closed:

1) VBScript to copy current file to backed up filename
2) in that VBScript, there was a 10 second wait
3) Shell out the VBScript using vba in the Excel file in its Close event

Technically, you could do something like this with Access too not any different really.

If you are just trying to back up the back end, I would probably create something to be called by windows task scheduler on a server if possible.
Or just double check with your I.T. dept...........I can't tell you how many places I've worked where the people had literally no idea that server backups, available/accessible in the form of Folder Properties>Previous Versions, was already turned on...and there was virtually no need to perform more backups.
Hi Isaac, I am trying to backup just my backend, where tables of the data reside. Do you have any idea for the simple method, I am not so good in MS Access, just learning thru youtube and from you guying in the community.

This is the module that I can call from backup button in the Admin Form, but it has error 429 says : Active X can can not create the file.
Code:
Option Compare Database
Public Function SimpleLogin_A_R1() As Boolean
Dim Source As String
Dim Target As String
Dim objFso As Object
Dim Path As String

Source = CurrentDb.Name
'Path = CurrentProject.Path
Path = "C:\TestDB"
Target = Path & "\BackupDB_" & Format(Now(), "mm-dd-yyyy") & ".accdb"

Set objFso = CreateObject("Scripting.FileSystenObject")

If objFso.folderExits(Path) Then
    a = objFso.copyfile(Source, Target, True)
Else
    objFso.Createfolder (Path)
    a = objFso.copyfile(Source, Target, True)
End If
Set objFso = Nothing
Set a = Nothing

End Function
 

Isaac

Lifelong Learner
Local time
Today, 05:37
Joined
Mar 14, 2017
Messages
8,738
that's because you have an misspelling in FileSystenObject, n instead of m

what you want to do is extremely trivial, the copying of a file. so should be OK. except you may not be able to copy the backend during such time as anyone is actively linked to it, actually on this point i can't remember, due to Previous Versions & sql server it's been a long time since i needed to back up access data.
 

hfsitumo2001

Member
Local time
Today, 05:37
Joined
Jan 17, 2021
Messages
365
that's because you have an misspelling in FileSystenObject, n instead of m

what you want to do is extremely trivial, the copying of a file. so should be OK. except you may not be able to copy the backend during such time as anyone is actively linked to it, actually on this point i can't remember, due to Previous Versions & sql server it's been a long time since i needed to back up access data.
Thank you Isaac, you are awesome... it works like a chant, after I also change objFso should have been objFSO, upper case for FSO. Ok this is my trial, now I want to learn how to make an automatic back up at mid night, to back up the back end only, because the data is there. Again thank you for your help, because actually I just followed what is explained on the Youtube
 

hfsitumo2001

Member
Local time
Today, 05:37
Joined
Jan 17, 2021
Messages
365
that's because you have an misspelling in FileSystenObject, n instead of m

what you want to do is extremely trivial, the copying of a file. so should be OK. except you may not be able to copy the backend during such time as anyone is actively linked to it, actually on this point i can't remember, due to Previous Versions & sql server it's been a long time since i needed to back up access data.
Isaac, I do not understand, on my trial database it works, but on my actual database it did not work with the compile error. Could you help where am I wrong?
Below is the picture:
compile error.jpg
 

Isaac

Lifelong Learner
Local time
Today, 05:37
Joined
Mar 14, 2017
Messages
8,738
is that thing you are calling the same as the name of a Module?
 

hfsitumo2001

Member
Local time
Today, 05:37
Joined
Jan 17, 2021
Messages
365
is that thing you are calling the same as the name of a Module?
Yes Isaac, but please ignore my question, because the problem is I thought the name of of the function should be the name of the database, even that is that makes it wrong. I misunderstood the sample given in the youtube.

If you have the sample of the module that can automatically save tables in server at midnight. Thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
If you have the sample of the module that can automatically save tables in server at midnight. Thank you.
You shouldn't need a module. The Task Scheduler should be able to do the job for you. Just a thought...
 

Isaac

Lifelong Learner
Local time
Today, 05:37
Joined
Mar 14, 2017
Messages
8,738
Yes Isaac, but please ignore my question, because the problem is I thought the name of of the function should be the name of the database, even that is that makes it wrong. I misunderstood the sample given in the youtube.

If you have the sample of the module that can automatically save tables in server at midnight. Thank you.
What I would do personally is write a VBScript to do the file copy (or do it in Access if you are more comfortable there), then create a Scheduled Task to run at midnight and execute the VBScript, or, run the Access sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
What I would do personally is write a VBScript to do the file copy (or do it in Access if you are more comfortable there), then create a Scheduled Task to run at midnight and execute the VBScript, or, run the Access sub
Although I haven't tested it, I was wondering if a simple xcopy command will do without having to create a batch or script file. Just thinking out loud...
 

hfsitumo2001

Member
Local time
Today, 05:37
Joined
Jan 17, 2021
Messages
365
What I would do personally is write a VBScript to do the file copy (or do it in Access if you are more comfortable there), then create a Scheduled Task to run at midnight and execute the VBScript, or, run the Access sub
Could you give me your sample of VBScript Isaac?
 

hfsitumo2001

Member
Local time
Today, 05:37
Joined
Jan 17, 2021
Messages
365
Although I haven't tested it, I was wondering if a simple xcopy command will do without having to create a batch or script file. Just a thinking out loud...
OK DBGuy, I will look into it. 14 years ago I think you helped me on making Church membership database back up, but let me try to dig where is it in my laptop.
 

Users who are viewing this thread

Top Bottom