How to automate database backup

12 New Member

I'm creating a database to store records of user accounts and want to have a way to automate backups. I know Access has the backup database function. Is it possible to use that so that it backups on some sort of regular basis, say every day at 5PM or something? Or would this be more of a Windows function?

Mar 4 '11 #1

Subscribe Reply

7838

Rabbit

12,516

Recognized Expert Moderator MVP

Since you can't ensure that Access will be running all the time, I would schedule the backup through Windows.

Mar 4 '11 #2

beacon

579

Contributor

I second Rabbit's suggestion. I use Windows' Scheduled Tasks and a VBScript to backup the databases I maintain.

If you'd like a sample script, I'll be happy to post one.

Mar 5 '11 #3

neelsfer

547

Contributor

Thx it would be nice to get it Beacon. I am looking for code to backup with vba

Mar 6 '11 #4

beacon

579

Contributor

The code isn't VBA...it's VBScript. Like Rabbit mentioned, the more reliable way to schedule the backup is using Windows, specifically Windows Scheduled Tasks.

I use a VBScript that I've placed in my C:\Temp folder and have a Windows schedule set to run it everyday at 6:00 AM. I also have written a subroutine that emails me if the script runs to completion. I guess technically it doesn't necessarily guarantee that the database will be backed up, but it has worked for me for a few years now.

I should mention that the code backs up the database every day for a maximum of 14 days. The 15th backup is deleted in an effort to save space...you can always remove that part of the code if you want to save ALL copies of the database.

Here's sample code (this code needs to be written in a text editor, like Notepad, and save with a .vbs extension...you'll also need to change the paths, folder names, and email info to match your own):

Expand|Select|Wrap|Line Numbers

 
'*********************

'*  Main Code Block  *

'*********************
 
dim strDriveLetter, strRemotePath, objNetwork, objMessage
 
strDriveLetter = "Z:"

strRemotePath = "YOUR_PATH_HERE"    'You need the literal path, not just the drive letter
 
set objNetwork = CreateObject("WScript.Network")
 
On Error Resume Next 'if drive is already mapped, go to next statement

objNetwork.MapNetworkDrive strDriveLetter, strRemotePath
 
Call BackUpDatabase
 
Call CleanUpFolder
 
Call SendEmail
 
'*****************

'*  End of Main  *

'*****************
 
Sub BackUpDatabase()
 
    dim strDriveLetter, strRemotePath, objNetwork

    dim mydate, location, path, filename

    dim filesys, destination
 
    strDriveLetter = "Z:"

    strRemotePath = "YOUR_PATH_HERE"
 
    set objNetwork = CreateObject("WScript.Network")
 
    On Error Resume Next 'if drive is already mapped go to next statement

    objNetwork.MapNetworkDrive strDriveLetter, strRemotePath
 
    mydate = replace(date, "/", "-")
 
    location = "FOLDER_PATH_AND_NAME_OF_THE_DATABASE_THAT_NEEDS_TO_BE_BACKED_UP"

    path = "PATH_WHERE_THE_DATABASE_WILL_BE_BACKED_UP+MINUS_THE_FILE_NAME"

    filename = "DATABASE_BACKUP_COPY_NAME"
 
    set filesys=CreateObject("Scripting.FileSystemObject")
 
    destination = path & filename & mydate & ".mdb"
 
    If filesys.FileExists(location) Then

       filesys.CopyFile location, destination

    End If
 
    objNetwork.RemoveNetworkDrive strDriveLetter
 
End Sub
 
Sub CleanUpFolder()
 
    dim path, filename, filesys, choice

    dim myArray()

    redim myArray(45)

    dim counter, dcounter, thedate, rdate
 
    path = "PATH_WHERE_THE_DATABASE_WILL_BE_BACKED_UP+MINUS_THE_FILE_NAME"

    filename = "DATABASE_BACKUP_COPY_NAME"
 
    set filesys=CreateObject("Scripting.FileSystemObject")
 
    dcounter = 60
 
    for counter = 1 to ubound(myArray)

        thedate = date - dcounter
 
        rdate = replace(thedate, "/", "-")
 
        rfile = path & filename & rdate & ".mdb"
 
                if filesys.fileexists(rfile) then

                    filesys.deletefile rfile

        end if
 
        dcounter = dcounter - 1

    next
 
End Sub
 
Sub SendEmail()
 
    dim htmlOpen, htmlFontOpen, myText, htmlFontClose, htmlClose
 
    htmlOpen = "<HTML><Body>"

    htmlFontOpen = "<Font Face=Verdana Size=2 Color=Navy>"

    myText = "Script Successful..."

    htmlFontClose = "</Font>"

    htmlClose = "</Body></HTML>"
 
    Set objMessage = CreateObject("CDO.Message") 
 
    With objMessage

        .Subject = "Scheduled Task Successful " & date & " -- YOUR DATABASE NAME"

        .From = "YOUR_EMAIL_ADDRESS" 

        .To = "YOU_EMAIL_ADDRESS" 

        .HTMLBody = htmlOpen & htmlFontOpen & myText & htmlFontClose & htmlClose
 
        '==This section provides the configuration information for the remote SMTP server.

        '==Normally you will only change the server name or IP.

        .Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
 
        'Name or IP of Remote SMTP Server

        .Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR_EXCHANGE_SERVER"
 
        'Server port (typically 25)

        .Configuration.Fields.Item _

        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
 
        .Configuration.Fields.Update
 
        '==End remote SMTP server configuration section==
 
        .Send
 
    End With
 
End Sub

Mar 7 '11 #5

Similar topics

PHP and MySql database backup

by: Konrad | last post by:

I have an internet portal based on MySql database and what I need is to make a database backup , after each actualization of data. I know how should PHP code look like but I have no idea how to...