Backup backend using batch file (1 Viewer)

[diberlee]

Hi,

I've created some code to make a backup of my database backend. I've tested it and it seems to work fine, I just have some questions about it's use. Here's the code:

Public Sub backupBackend()
Dim TestFile As String
Dim strReplFile As String
Dim copyFromLocation As String
Dim copyToLocation As String
copyFromLocation = "S:\Trust 2 Analysis\Data"
' set the file name and location of the file to copy
copyFromLocation = copyFromLocation & "\trust2_be.accdb"
'set the location/name of the file that will be written
copyToLocation = "S:\Trust 2 Analysis\Data\Backups\trust2_be " & Format(Now(), "dd-mm-yy hh-mm-ss") & ".accdb"
' set the file name of the batch file to create
TestFile = CurrentProject.Path & "\BackupDbBE.cmd"
' create the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Backing up database"
Print #1, ""
Print #1, "ECHO Copying file to backup folder"
Print #1, "Copy /Y """ & copyFromLocation & """ """ & copyToLocation & """"
Print #1, ""
Print #1, "DEL " & """%~f0"""
Close #1
' run the batch file
Shell TestFile
End Sub

It will be triggered by a button push. It creates a batch file to do the copy operation and then delete itself.

In an ideal world I would schedule the backup at a safe time through the Windows scheduler, but our IT department won't allow me to do so.

The concern I have is that we could potentially press the backup button while a user is performing an action that writes/modifies data. If this were to happen would my backup potentially contain corrupted data?

If so, is the workaround to simply press the backup button when nobody is using the database?

Regards
Duane

 

[spikepl]

1. You need all users to be disconnected from the backend incl. the frontend you are in yourself.
2. You can check if all are disconnected by checking for existence of YourBackendName.lccdb file, which only exists if someone is connected to the backend.
3. You can write code in the frontend that executes on closing of the frontend. If no-one else is in the backend then it takes backup of the backend. This means the last one to log out triggers a backup. If stragglers do not logout then no backup.

 

[JackKaptijn]

I agree with Spikepl. Check if nobody is using the database.
You do not need a batch file.
This works also....

 Dim fso As New scripting.FileSystemObject
If fso.FolderExists(Me.AppPath & "\Backup\") = False Then fso.CreateFolder (Me.AppPath & "\Backup\")
fso.CopyFile Me.DatabasePath, Me.AppPath & "\Backup\", True

 

[diberlee]

Thanks both for your responses. I'll look to implement Jack's suggestion within Spike's guidelines.

Which event should I be working with to run code when the FE has closed the connection to the BE?