Database Backup Restore From Desktop ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We have already discussed two important topics earlier:

Database Daily Backup – a procedure that runs automatically from within the database the first time it is opened each day. It ensures one backup per day and avoids repeated backups on subsequent openings or closings.
Compacting and Repairing Databases – using a dedicated “maintenance” database with VBA programs to compact and repair multiple databases listed within it, one after another.

Apart from these, Microsoft Access itself provides a built-in option to compact and repair the current database automatically on close.

A New Approach: Backup and Restore with VBScript

The new method we will explore is using VBScript files, saved on the desktop, to perform manual backup and restore operations. Unlike the first two methods, this is not an automated process. Instead, you simply double-click a desktop shortcut whenever you want to:

Create a backup of your database, or
Restore the database from an earlier backup if you encounter corruption or consistency issues.

VBScript (Visual Basic Scripting Edition) is a lightweight version of Visual Basic, often used in web pages and Windows automation. If you are familiar with VBA, you will find the syntax quite straightforward.

Preparing the Backup Script

Open Notepad on your desktop.
Paste the VBScript code (provided in the next section) into the empty file.
Save the file as:
```
CreateBackup.vbs
```
Make sure the extension is .vbs and not .txt.
Right-click the new file and choose Edit if you want to make changes later.
To run the script, simply double-click the file.

Similarly, you can create another script named RestoreBackup.vbs to restore the database from the last saved copy.

The CreateBackup VB Script:

Call CreateBackup()

Sub CreateBackup()
'======================================================
'Desk-top Shortcut Name: CreateBackup.vbs
'Language              : VBScript - Creates File Backup
'Remark                : Run from Desktop Shortcut
'                      : Edit Backup Path
'======================================================
Dim s, t, p, a
Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Backup is taken in the File Folder itself
' with Day and Month added (-dd-mm) to the File Name.
' Example: D:\AccTest\Testdb-16-07.accdb

p = "D:\AccTest\NorthWind.accdb" 'Edit

s = InputBox("File PathName:","CreateBackup()" , p)

If objFSO.FileExists(s) Then
  'File Name changes appending with -dd-mm (-day-month) values.
    a = Left(Now(), 5)
    t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))

'Create the File backup
    a = objFSO.CopyFile(s, t, True)

    MsgBox "Backup Successful!" & vbCr & vbCr & "Source: " & s & vbCr & "Backup: " & t,vbInformation,"CreateBackup()"
Else
    MsgBox "Source PathName: " & s & vbCr & "Not Found, Program Aborted!",vbCritical,"CreateBackup()"
End If
Set objFSO = Nothing
End Sub

Database CreateBackup() Code Line-By-Line.

Understanding the Backup Script

The backup procedure is straightforward. Let us walk through it step by step.

The first statement:


Call CreateBackup()

invokes the CreateBackup() subroutine (shown below). This explicit call is required because when you run a VBScript by double-clicking its desktop shortcut (or by right-clicking and selecting Open), any code placed directly inside a Subroutine or Function will be ignored by the script processor unless it is explicitly invoked.

Important Notes:

Direct Script Execution
- You may also write the script directly in a Notepad file with the .vbs extension, without wrapping it in a Sub or Function.
- In that case, the script runs line by line from top to bottom when executed.

Functions in VBScript

The definition Function is valid, but only if you omit the return type declaration.

Examples:


Function CreateBackup()   ' ✅ Acceptable
End Function

Function CreateBackup() As Integer   ' ❌ Not allowed in VBScript
End Function

Variable Declarations
- VBScript does not allow explicit type declarations (e.g., Dim db_Pathname As String).
- All variables are treated like VBA Variants, meaning their type is determined automatically by the first value assigned to them.
- If you are embedding VBScript logic inside VBA (for example, calling it from within Access), then you may use normal VBA-style declarations.

This ensures readers clearly understand:

why you used Call CreateBackup(),
the difference between .vbs free-flow scripts vs. subroutine/function structures, and
How variable handling differs from VBA.

Set objFSO = CreateObject("Scripting.FileSystemObject")

Breaking Down the Backup Script

Creating the File System Object
```
Set objFSO = CreateObject("Scripting.FileSystemObject")
```
This line creates a File System Object (FSO) and assigns it to the variable objFSO. The FSO provides access to files and folders on your computer (similar to VBA’s FileSystemObject).
Setting the Database Path
```
p = "D:\AccTest\NorthWind.accdb"
```
The variable p holds the database pathname.
- This value is shown as the default path in the InputBox() prompt.
- If you always back up the same database, leave p set to the fixed path.
- If you want flexibility, you can edit the pathname directly in the InputBox() prompt each time you run the script.
Checking if the Database File Exists
```
If objFSO.FileExists(s) Then
```
The FileExists() Method checks whether the source file (the database you want to back up) actually exists at the provided path.
- If the file exists, the script proceeds with creating the backup.
- If not, the script can return an error message (or do nothing).
Creating the Backup File Name
If the database is found, the backup file name is generated by appending the current day and month in -dd-mm format to the original file name.

Example:
```
D:\AccTest\NorthWind-16-07.accdb
```
Here, 16-07 indicates the backup was created on July 16th.

    a = Left(Now(), 5)
    t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))

Copying the Database File

The following statement performs the actual backup by calling the CopyFile() method of the objFSO object:


a = objFSO.CopyFile(s, t, True)

s (Source File): The full pathname of the database file you want to back up.
t (Target File) The new file name, including the date suffix, was created in the same folder.
True (Overwrite Option): If the target file already exists, it will be overwritten without warning.

Once the copy operation is completed, the script displays a confirmation message indicating that the backup was successful.

If the source file (s), is not found, the program halts and displays a critical error message.

⚠️ Important Note:

If you run the backup procedure more than once on the same day, the backup file created earlier will be overwritten automatically, since the overwrite parameter is set to True. If you want to keep multiple backups per day, you’ll need to modify the naming convention (for example, by adding hours and minutes to the file name).

Database RestoreFile() VBScript Code.

Creating the Restore Script

Follow the same procedure you used earlier for creating the backup script file in Notepad:

Open Notepad.
Copy and paste the Restore Script code (given below) into the new file.
Save the file with the name RestoreFile.vbs on your Desktop.
- Ensure the extension is .vbs (not .txt).
A Desktop Shortcut with the .vbs extension will appear, representing the Restore Script.

Whenever you need to restore your database from a backup, simply double-click the RestoreFile.vbs shortcut.

Paste the following code into your Notepad file:

Call RestoreFile()

Sub RestoreFile()
'================================================
'Language              : VBScript
'Desk-top Shortcut Name: RestoreFile.vbs 
'Remarks               : Restore File from Backup
'================================================
Dim db_Current
Dim db_Save
Dim db_Backup
Dim f_bkSource,f_Current, f_Save
Dim objFSO

'The following three demo lines can be replaced
'in the InputBox statement, to Input
'File Pathnames directly.
db_Current = "D:\AccTest\NorthWind.Accdb"      'The file needs replacement
db_Save = "D:\AccTest\NorthWind-Save.Accdb"    'save the [dbReplace] file with a new name
db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore from this Backup File

f_bkSource = InputBox("Backup File PathName:","Restore()",db_Backup)
f_Current = InputBox("Restore File PathName:","Restore()",db_Current)
f_save = InputBox("Save Current before replace:","Restore()",db_save)

'Create File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
    a = objFSO.CopyFile(f_Current, f_save, True)
    a = objFSO.DeleteFile(f_current) 'delete original file after copying with a new name
End If

'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then 
	a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.

  If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
   	MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
  Else
   	MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
  End If 
Else
   MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If

Set objFSO = Nothing
End Sub

RestoreFile() Code Line-by-Line.

@@@

RestoreFile.vbs (with Save-before-Restore logic)


Call RestoreFile()

Sub RestoreFile()
    Dim objFSO
    Dim db_Current, db_Save, db_Backup
    Dim f_Current, f_Save, f_bkSource

    ' Default pathnames (can be accepted or changed by user)
    db_Current = "D:\AccTest\NorthWind.accdb"       ' File to be replaced
    db_Save    = "D:\AccTest\NorthWind-Save.accdb"  ' Save old version before overwrite
    db_Backup  = "D:\AccTest\NorthWind-17-07.accdb" ' Backup to restore from

    ' Ask user for actual file pathnames
    f_Current  = InputBox("Enter CURRENT database file to be replaced:", _
                 "Restore Database", db_Current)

    f_Save     = InputBox("Enter SAVE file name for current database:", _
                 "Restore Database", db_Save)

    f_bkSource = InputBox("Enter BACKUP database file to restore from:", _
                 "Restore Database", db_Backup)

    ' Create File System Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    ' 1. Check if Current DB exists, and Save it first
    If objFSO.FileExists(f_Current) Then
        objFSO.CopyFile f_Current, f_Save, True
    Else
        MsgBox "Current database not found: " & vbCrLf & f_Current, vbCritical, "Restore Failed"
        Exit Sub
    End If

    ' 2. Check if Backup file exists, then restore
    If objFSO.FileExists(f_bkSource) Then
        objFSO.CopyFile f_bkSource, f_Current, True
        MsgBox "Database restored successfully from:" & vbCrLf & f_bkSource & vbCrLf & _
               "to:" & vbCrLf & f_Current & vbCrLf & vbCrLf & _
               "Old database saved as:" & vbCrLf & f_Save, vbInformation, "Restore Complete"
    Else
        MsgBox "Backup file not found: " & vbCrLf & f_bkSource, vbCritical, "Restore Failed"
    End If
End Sub

🔑 What happens here:

Defaults are preloaded (db_Current, db_Save, db_Backup), so the InputBoxes show usable pathnames right away.
The user can simply press Enter to accept them or edit them on the fly.
Step 1: The Current DB is first copied to a “safe” file (…-Save.accdb) before it’s overwritten.
Step 2: If the backup file exists, it overwrites the original DB with the chosen backup.
Messages clearly indicate what happened and where the files went.

If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
	a = objFSO.CopyFile(f_Current, f_save, True)
        a = objFSO.DeleteFile(f_current) 'delete the original after copying with a new name
        
 End If

The above code segment checks whether the current file specified for replacement exists. If it does, the file is first copied with a modified name for safekeeping. If the file is not found, this step is skipped. Note that the CopyFile method does not return a status value in the variable, even if you assign it, as in a = objFSO.CopyFile(...). This syntax simply allows the use of parentheses around the parameters. The following statement is equally valid and often clearer:

objFSO.CopyFile f_Current, f_save, True

Next, the corrupt file is removed using the DeleteFile() method of the objFSO object.

The following code segment then performs a validation check with the FileExists() method on the backup database file. If the file is found, it is copied back using the original database name—overwriting any existing file. Since the original was deleted in the previous step, this ensures the restored backup replaces it cleanly.

'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then 
	a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.

  If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
   	MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
  Else
   	MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
  End If 
Else
   MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If

After the restore operation, the program verifies the presence of the original database file to confirm that the restore was successful. If the file is found, a success message is displayed. If the copy operation fails, however, a critical error message is shown.

If the specified backup file cannot be located, the program immediately displays a critical error message and releases the objFSO object from memory before terminating.

Tip: You can back up and restore any type of file besides databases.