Solved Database backup

[prasadgov]

Hi,

I am trying this batch file to backup my database but it is not creating the backup, when I tested it manually

@echo off

REM Set the path to your Access database
set dbPath=C:\Dailyreport\CurrentDB.accdb

REM Set the path to the backup folder
set backupFolder=W:\common\Reports\dbbackup

REM Get the current date and time
for /f "tokens=1-4 delims=/ " %%a in
 ("%date%") do (
 set day=%%a
set month=%%b
set year=%%c
 )

for /f "tokens=1-2 delims=:" %%a in
 ("%time%") do ( 
set hour=%%a set minute=%%b
 )

REM Format the date and time (e.g., YYYYMMDD_HHMM)

set formattedDate=%year%%month%%day%
set formattedTime=%hour%%minute%

REM Create the backup file name

set backupFile=%backupFolder%\CurrentDB_backup_%formattedDate%_%formattedTime%.accdb
REM Perform the backup
copy "%dbPath%" "%backupFile%"

echo Backup completed successfully:
 %backupFile%

Not sure why.

TIA

 

[Isaac]

You could probably write this in vbscript and accomplish the same thing with somewhat simpler code

 

[Gasman]

Have you checked your have the variables?
Comment out the @echo off and use it to debug.

 

[theDBguy]

Where did you get this batch file from? Just to troubleshoot, try using a hard-coded date for now, to rule it out.

 

[MarkK]

Easiest to do this In VBA, in the database itself...

Sub BackupCurrentDatabase()
    Const BACKUP_FILE = "W:\common\Reports\dbbackup\DB_BU_{0}.accdb"
    
    With CreateObject("Scripting.FileSystemObject")
        .CopyFile CurrentDb.Name, VBA.Replace(BACKUP_FILE, "{0}", Format(Now, "yyyymmdd_hhnn"))
    End With
End Sub

 

[Isaac]

I would have assumed the copy would either fail or render an un-useable accdb, since the file is in use - but you're right, that seems to work.
Feel like I remember this not working in the past...but that could be a false memory. That was why I suggested an after-the-fact script instead.
I would still feel worried although it seemed to work ok on my test..
It doesn't work with all office files, but then again excel for instance has SaveAsCopy which effects the same thing.

 

[The_Doc_Man]

The self-backup works if and only if no one else is doing any updates in the DB tables AND the self-backup is the only thing YOU are doing AND as long as you have either Optimistic Locks or No Locks on anything else that might be open at the moment.

Otherwise the file locking will get in the way.

 

[Gasman]

I used to use this when I was developing, from within the DB
It was run from a switchboard menu, so I just passed in FE or BE, depending on what I wanted to backup.

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
    
    
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("tbl-version_fe_master")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
    
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
    
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
    
    
End Sub

Function CreateBackupFE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("FE")
End Function
Function CreateBackupBE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("BE")
End Function
Public Function GetBackEndPath() As String
On Error GoTo Err_Handler
    Const Attached = dbAttachedTable Or dbAttachedODBC
    Dim dbs As Database
    Dim tbl As TableDef
    Set dbs = DBEngine(0)(0)
    For Each tbl In dbs.TableDefs
        ' Gets the back end full path
        Debug.Print tbl.Name
        Debug.Print tbl.Connect
        If (tbl.Attributes And Attached) <> 0 And Left(tbl.Connect, 10) = ";DATABASE=" Then
            GetBackEndPath = Mid(tbl.Connect, 11, Len(tbl.Connect) - 10)
            Exit For
        End If
    Next

    Set dbs = Nothing
    Set tbl = Nothing
    
Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox (Err.Number & " " & Err.Description & " " & "SelectAll()")
    Resume Exit_Handler
    
End Function

Function GetAccessBE_PathFilename(pTableName As String) As String
'strive4peace

   ' RETURN
   '  the file path and file name of the BE database
   '  "" if the table is not linked
   
   On Error GoTo PROC_ERR
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
   
   GetAccessBE_PathFilename = ""
   
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
   
   If Len(tdf.Connect) = 0 Then
      GoTo PROC_EXIT
   End If
   
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo PROC_EXIT
   End If
   
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
    
PROC_EXIT:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
  
PROC_ERR:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"

   Resume PROC_EXIT
   Resume
             
End Function

 

[Isaac]

The real challenge comes if you use access for a back end (sadly), and want to back up the back end now and then.
That needs to have users out of it.
Unless you work at a company where PreviousVersions is turned on, in which case most of your work is done for you.

 

[MarkK]

The real challenge comes if you use access for a back end (sadly), and want to back up the back end now and then.
That needs to have users out of it.
Unless you work at a company where PreviousVersions is turned on, in which case most of your work is done for you.

The self-backup works if and only if no one else is doing any updates in the DB tables AND the self-backup is the only thing YOU are doing AND as long as you have either Optimistic Locks or No Locks on anything else that might be open at the moment.

Otherwise the file locking will get in the way.

My FE does auto-backups on the BE routinely. I have been doing this for probably 15 or 20 years in a multi-user environment with an MS Access file as the BE on a server. There has never been an issue. I take copies of the BE home with me on a flash-drive, routinely making that copy while users are logged in. No problem.

 

[The_Doc_Man]

I have been doing this for probably 15 or 20 years in a multi-user environment with an MS Access file as the BE on a server. There has never been an issue.

While I absolutely don't doubt you, I also have to say that you are lucky. Also, the time of day would make a difference. Most databases will not behave well when the tables are active. ORACLE wouldn't. ShareBase (eventually evolved into Sybase) wouldn't. I never had the chance to try SQL Server so won't make a comment. Access shouldn't work right if users are active, because the file locks in an actively updating system would be all wrong. You need a quiescent system to make an online backup.

ORACLE solved the problem by having a mode of operation that supported a quiescent backup even though the DB was still active. In essence, they "froze" the DB and accumulated transactions while the DB was going on, but didn't commit them until the DB Backup mode was changed back to normal mode.

ShareBase never did solve that problem while they were on site with us, though I believe Sybase has a backup mode or equivalent.

 

[ebs17]

There has never been an issue. I take copies of the BE home with me

Creating a copy will be less of a problem. However, you don't know whether this copy is error-free and can be used 100% as a backup. You shouldn't just assume something like that, you should check it explicitly. If you really need a backup, it should also be usable.

Simple rule of thumb: In order to create a backend (access file) securely, you should have exclusive access to this backend.

 

[prasadgov]

Easiest to do this In VBA, in the database itself...

Sub BackupCurrentDatabase()
    Const BACKUP_FILE = "W:\common\Reports\dbbackup\DB_BU_{0}.accdb"
   
    With CreateObject("Scripting.FileSystemObject")
        .CopyFile CurrentDb.Name, VBA.Replace(BACKUP_FILE, "{0}", Format(Now, "yyyymmdd_hhnn"))
    End With
End Sub

The backup is done in the scheduler everyday at 10.00 pm and there is no User in it. I'll try to just copy the database without opening it, as in below

@echo off
echo Copying Database.....
pause
Set NewDate=%date:/=%
copy "C:\Dailyreport\CurrentDB.accdb" "W:\common\Reports\dbbackup\CurrentDB%newdate%.accdb"

 

[Isaac]

My FE does auto-backups on the BE routinely. I have been doing this for probably 15 or 20 years in a multi-user environment with an MS Access file as the BE on a server. There has never been an issue. I take copies of the BE home with me on a flash-drive, routinely making that copy while users are logged in. No problem.

Yeah in my experience you can make the copy perhaps and think that everything is okay but you can't necessarily be certain that you'll be able to open that copy and you certainly can't know what state certain records we're in while people were using it while you made the copy. It's definitely best to back up the back end when it's not in use. Therein lies the challenge.

I've made many copies of things that I found out later weren't usable or weren't accurate because I should never have tried to make the copy while it was in use

 

[The_Doc_Man]

The backup is done in the scheduler everyday at 10.00 pm and there is no User in it. I'll try to just copy the database without opening it, as in below

@echo off
echo Copying Database.....
pause
Set NewDate=%date:/=%
copy "C:\Dailyreport\CurrentDB.accdb" "W:\common\Reports\dbbackup\CurrentDB%newdate%.accdb"

As long as nobody has stayed logged in to the DB at the time this batch job runs, you have a good chance. There is a good reason to do it this way rather from inside the DB... locks. If, as you say, no users are in it, then there will be no internal locks for Access to arbitrate AND the only external (file) lock will be yours when you make the copy. That is as favorable as it gets.

 

[Gasman]

TBH, it has been a while, but I used to backup the BE quite regularly using my method method, and do not remember having any issues.
That said, I never had to try and use that backup?

Of course if there are you have to ensure that all users are logged out?
Another user here has found out a MSGBOX was stopping his idlecode to kick the user out happening.

 

[Isaac]

That said, I never had to try and use that backup?

Yep