Any new methods of database backup.

[TOPSie]

I am supporting a small organisation which runs an Access application. The (back-end) database is backed up at close of business every night, both local copies and to the cloud.


The danger is that a failure at 5pm (say) means the potential loss of all the day's transactions.

Is anyone aware of any current products which will do (incremental?) backups throughout the day - without affecting performance - ?

or any general tips on this area :-)

[ranman256]

paste this code into a module, then run : BackupDb
it will copy the backend access db to the backup folder

Public Function BackupDb()
Dim vDir, f, vExt, vTarg, vDb
Dim vSuffx
Const kTargetDir = "\\server\BackupFolder\"
   vDb = "\\server\programs\BackendDB.accdb"
   'vDb = CurrentDb.Name
   
   If vDb <> "" Then
        getDirName vDb, vDir, f
        vExt = Mid(f, InStrRev(f, "."))
    
        vSuffx = "_Backup" & Format(Now(), "yymmdd-hhnnss") & vExt
        
        'BACKEND TABLES
          'Path and file name for new mdb file
         vTarg = kTargetDir & f & vSuffx
         Copy1File vDb, vTarg
   End If
     
     'FRONT END APP
   MsgBox "Done", vbInformation, "Backup"
End Function

Public Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
    'psFilePath: full file path given
    'prvDir : directory name output
    'prvFile: filename only output
Dim i As Integer, sDir As String
i = InStrRev(psFilePath, "\")          'not available in '97
If i > 0 Then
  prvDir = Left(psFilePath, i)
  prvFile = Mid(psFilePath, i + 1)
  ''If Asc(Mid(prvFile, Len(prvFile), 1)) = 0 Then RemoveLastChr prvFile
End If
End Sub

Public Function Copy1File(ByVal pvSrc, ByVal pvTarg) As Boolean
Dim fso
On Error GoTo errMake
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile pvSrc, pvTarg
Copy1File = True
Set fso = Nothing
Exit Function
errMake:
'MsgBox Err.Description & vbCrLf & pvSrc, , "Copy1File(): " & Err
Set fso = Nothing
End Function

[TOPSie]

Well yes - this is essentially what happens at close of business.
I am looking for something to keep copying (changes??) at regular intervals through the day.
As I understand it your suggestion would require every user to stop and close their connection whilst this copy is happening to avoid inconsistencies.

[Welshgasman]

Not new
Just create a batch/cmd file to make the copy.
Schedule it to run each hour (or whenever you want) with the windows scheduler.
You can append the date and time to the filename so you know which one to choose.

Perhaps tidy up at the end of each week?

[TOPSie]

The Microsoft documentation says - "Notify users before you start the backup process because running the backup process requires exclusive access to the database file, and users might be unable to use the back-end database while the backup process is running."

So I am very nervous about using this timer job approach.

[Welshgasman]

Well you cannot have it both ways.
Try it out with everyone knowing not to enter any data, just view data for a set period.
I tested it with my single db whilst in it, and it copied fine.
You can even test it yourself, with just you in the db?

[Micron]

Maybe provide a link to the information so that we can review? This one only indicates that users should be out so that up to date data is backed up. Unless I missed it, it says nothing about requiring exclusive access.

https://support.microsoft.com/en-us/...a-ee81f8d6356c

[Welshgasman]

Just tested on one of my be dbs and it copied fine.

[TOPSie]

Maybe it is just semantics - but that link is one I have already quoted from - "If your database has several users, before you perform a backup make sure that all users close their databases so that all changes to the data are saved."

This to me is a definition of "you require exclusive access" before you do a backup. In my situation there are only 2 users - but their working pattern can't be predicted, so a timer task is not the answer

[Welshgasman]

Not to me it doesn't, just that you will likely save the db when someone is halfway through entering data?
Your choice, save data when you can, or just at the end of the day. You could even save at lunchtime, but no guarantee that someone does not leave a form half completed and go to lunch.

[moke123]

I use this in the OnLoad of a unbound Log In form.
It iterates through a folder of backups and if the most recent is a week old it creates a new one. It uses fso to read the date created property. It will also delete any backups which are older than 30 days.
You can change the number of days to anything you want.

Public Sub BackEndBackUps()
    
    Dim fld As Folder, fil As File
    Dim dteMax As Date, flg As Boolean
    Dim objFiles As Object, lngFileCount As Long
    Dim FolderPth As String
    
    Dim fso As New FileSystemObject


    FolderPth = CurrentProject.Path & "\DataBackups" 'path to backup folder


    'If the backup folder doesn't exist, create it
    If Len(Dir(FolderPth, vbDirectory)) = 0 Then
        MkDir FolderPth
    End If


    dteMax = #1/1/2000#


    Set fld = fso.GetFolder(FolderPth)


    'get the most recent created date of all the backups in the folder
    For Each fil In fld.Files
        If dteMax < fil.DateCreated Then
            dteMax = fil.DateCreated
        End If
    Next


    Set objFiles = fso.GetFolder(FolderPth).Files


    lngFileCount = objFiles.Count


    'check if latest backup is older than a certain number of days
    For Each fil In fld.Files


        If DateDiff("d", dteMax, Date) > 6 Then   'you can change the numeric value to the number of days between backups
        
            flg = True


        End If


    Next


    If flg = True Or lngFileCount = 0 Then
        BackUpAndCompactBE FolderPth
    End If


    'if backup file is older than a certain number of days, delete it
    For Each fil In fld.Files


        If DateDiff("d", fil.DateCreated, Date) > 28 Then   'you can change the numeric value to the number of days before deleted


            fil.Delete


        End If
    Next


End Sub


Public Sub BackUpAndCompactBE(BackupFolder As String)
'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Modified by theDBguy on 5/27/2019
'Source: http://www.accessmvp.com/thedbguy


    On Error GoTo errHandler


    Dim strDestination As String
    Dim strSource As String
    Dim strTableName As String
    Dim strFileName As String
    Dim BUFolder As String
 
    'Create a file scripting object that will backup the db
    Dim oFSO As New FileSystemObject


    strTableName = "tblCases"    '*****MUST BE CHANGED TO A TABLE NAME IN LINKED BACK END


    'Get the source of your back end
    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _
                            "Database=")(1), ";")(0)


    ' strFileName = Format(Now, "mmddyyyy") & oFSO.GetFileName(strSource)  'append to beginning of file name
    strFileName = oFSO.GetBaseName(strSource) & Format(Now, "mmddyyyy") & "." & oFSO.GetExtensionName(strSource)  'append to end of file name


   ' BUFolder = CurrentProject.Path & "\DataBackUps"


    'Determine your destination
    strDestination = oFSO.BuildPath(BackupFolder, strFileName)


    'Flush the cache of the current database
    DBEngine.Idle


    'execute the backup
    oFSO.CopyFile strSource, strDestination
    Set oFSO = Nothing


    'Compact the new file, ...
    Name strDestination As strDestination & ".cpk"
    DBEngine.CompactDatabase strDestination & ".cpk", strDestination
    'Uncomment the following line and comment the previous line
    'if your backend file is password protected or if you want the backup to have a password
    'DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=YourPassword"
    Kill strDestination & ".cpk"


errExit:
    Exit Sub


errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit


End Sub

[twgonder]

Just tested on one of my be dbs and it copied fine.

Am I wrong to think that just copying a file is not going to get a good backup? Not knowing the dark inner workings of Access, I'm going to just guess and think that copying might leave a lot of unwritten stuff in memory, waiting to get to a hard disk, on either a FE or BE or both in one (non-split).

Since Jet/ACE aren't VSS ready, I'm guessing that stuff sitting in memory, waiting to be flushed, is a real possibility.
Has anyone heard if MS has plans to make ACE compatible with VSS? Or do we have to, forever more, go with SQL Server (or others) just to get a decent backup during the day?

[twgonder]

... but no guarantee that someone does not leave a form half completed and go to lunch.

Does Access, when split, write a partially completed form's data to the BE without actually doing an explicit save? If not, what could be a problem in the backup?
Assuming that the Exclusive part of the above documentation is all wet.

[twgonder]

I use this in the OnLoad of a unbound Log In form.
It iterates through a folder of backups and if the most recent is a week old it creates a new one.

Does this run for all the users every time they login? What if we want a BU every 1/2 hour or so?

'Flush the cache of the current database
    DBEngine.Idle

Is this line enough to get a good and complete backup that will restore without corruption? In other words, does it do all that MS built into the Exclusive save? How many times have you had to restore and use one of these files? Any complications? Was there lots of activity on the system when the user logged on? Or are we to assume the first user to hit the login form has no competition for resources (I've seen offices where everyone starts logging in at 1 minute before 8:00 am)?

[NoellaG]

easiest solution: move the backend tables to SQL server and use the build-in possibilities to make a full backup very night and log back-ups every hour or 15 minutes. No programming required, just use the standard features.