Database Daily Backup

Introduction

If your Database is installed on a Local Area Network (LAN) location, then a regular server backup is done on a Daily/Weekly/Monthly/Quarterly basis by the Network Team and kept them in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or being deleted by mistake, etc., you can always send a request to the Computer Department giving details of location, database name, and safe backup date from which you would like to restore. This may take a few hours to a few days to get done because the backup tapes or other mediums must be transported back from their storage location before they are able to complete your request.

If your project has its own backup procedure and does it on a regular basis, then you don't have to go after centralized Network backups, causing delays in restoring the Database. This will also ensure that your Application's downtime is very minimal.

You can secure the Database Objects (Forms, Queries, Tables, Reports, etc.) from within the Database but when it comes to the safety of the Database File this will not work. When the database is on a Network location several Users can have access rights to that folder, besides your Application Users, and the database is not safe there.

You cannot make a Database Read-Only under Network Security to protect it from inadvertent loss. If you do that then you cannot work with the Database.

Automatic Daily Backup

As a precautionary measure, we can take a quick Daily Backup of the Database File, from Server to Local Disk, or vice versa with a DOS Batch File. The Backup should run immediately on opening the Database for the first time of the day, by any one of the users.

We can do this with a simple VBA Routine to create a DOS Batch File in the database folder and run it from there to make a copy to the local drive. We need a small table with a single record to keep track of the Backup event. The backup program should run only once a day when the database is open for the first time on the day, by any one of the users and should prevent the program from running on subsequent shutdowns and re-opening events.

Preparations

1. Create a Table with the following structure and save it with the name Bkup_Ctrl and add a single record with a date earlier than today in the bkupdate Field. Leave the other field blank.

   Table : Bkup_Ctrl Structure

   Srl.	Field Name	Type	Size
   1.	bkupdate	Date/Time
   2.	workstation	Text	20

   
   

   Table : Bkup_Ctrl

   bkupdate	workstation
   01/05/2008	PC1-1234

2. Copy and Paste the following Code into a Global Module of your Project and save the Module.

   Public Function SysBackup()
   '------------------------------------------------------'
   'Author: a.p.r. pillai
   'Date  : 01-Apr-2008
   'URL   : http://www.msaccesstips.com
   'All Rights Resersed by msaccesstips.com
   '------------------------------------------------------
   Dim dbPathName, j As Long, t As Date
   Dim bkupdate, strBatchFlle As String, qot As String
   
   On Error GoTo sysBackup_Err
   
   qot = Chr$(34)
   bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)
   ' bkupdate+7 > date() for weekly backup
   If bkupdate = Date Or bkupdate = 0 Then
       Exit Function
   End If
   
   dbPathName = CurrentDb.Name
   'dbPathName = "\\ServerName\Accounts\MIS\MISDB.Accdb" 'If BE on LAN Server
   
   j = InStrRev(dbPathName, "\ ")
   
   If j > 0 Then
       strBatchFlle = Left(dbPathName, j)
       strBatchFile = strBatchFlle & "bakup.bat"
       Open strBatchFile For Output As #1
           Print #1, "@Echo off"
           Print #1, "Echo :------------------------- "
           Print #1, "Echo : " & dbPathName
           Print #1, "Echo Daily Backup to C:\ "
           Print #1, "Echo :------------------------- "
           Print #1, "Echo : "
           Print #1, "Echo :Please wait... "
           Print #1, "Echo : "
           Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\ " & qot
   'add lines here for Back-end database or for other Files
       Close #1
   
   'Copy file
       Call Shell(strBatchFile, vbNormalFocus)
       t = Timer
       Do While Timer <= t + 10 'increase for bigger database 
          DoEvents 'wait for 10 seconds to complete the process
       Loop
   
       DoCmd.SetWarnings False
       DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"        DoCmd.SetWarnings True
   
       'Kill strBatchFile
     End If
   
   sysBackup_Exit:
   Exit Function
   
   sysBackup_Err:
   MsgBox Err.Description, , "sysBackup()"
   Resume sysBackup_Exit
   End Function
   

3. Add the following line of code in the On_Load() Event Procedure of the Startup Screen or Main Switchboard or any other form that opens immediately after loading the Database.

SysBackup

How does it Work?

At the beginning of the SysBackup() routine, the Program reads the last backup date from the Bkup_Ctrl Table and checks whether it matches today's date, if it does then stops the program from proceeding further. By replacing the expression bkupdate = date() with the expression bkupdate+7 > date() you can schedule the Backup to run at weekly intervals on a particular Day of the Week.

The VBA Routine creates a DOS Batch File in the same folder of your Database and Runs it. The DOS Copy command is used for copying the Database File to the User's local drive. Even though a VBA FileCopy() Function is available, this will not work from within to make a copy of the same Database.

You may modify the line to change the Target Location C:\ to a different one if needed.

A delay loop is built into the routine to slow down the program for about 10 seconds, to give enough time for the DOS Command to complete copying the Database. Normally, the VBA Code execution will not wait for the DOS Command to complete before executing the next statement. This will also prevent the User from starting to work with the Database before the copy operation is complete. You may increase or decrease this value depending on the size of the Database, or after trial runs of the procedure to determine the approximate time it takes to copy.

The control table's bkupdate Field is updated with the current date immediately after completion of the Copy operation and this will prevent further running of this procedure in subsequent Sessions on the same day. If your Application has a Back-End Database then install this table in there and link it to the Front-End. If your Application is on a Network and shared by several Users then by referring to the workstation field you can easily find out which machine has the latest Backup Copy.

The Kill strBatchFile statement (if enabled) will delete the DOS Batch File after the backup operation. The delay loop protects the DOS Batch File from this statement for about 10 seconds. Enable this line if you don't want the batch file to remain in the database folder.

Create Batch File Manually.

You can create a DOS Batch file manually, with a Text Editor like Windows Notepad, install it in the Database folder and run it from the Code or Macro. You may define the Source, and Target Locations manually for the Copy command.

Portability Considerations

The advantage of the above Code is portability and convenience. You can copy the Code and the bkup_ctrl Table into your other Projects and run it without much change or worrying about the Source or Target Location addresses of the Database.

Download

Download Demo DailyBakup

• Crosstab Union Queries for Charts
• Union Query
• Percentage on Total Query
• Selecting Office Assistant
• Office Assistant with Check Box Menu