Automatic Backup - Access DB

[kacey8]

Hi everyone.

Just wanted to confirm which is the best way to create an automatic back up of a access DB. (Split DB, backing up the BE - Access2010)

Firstly I was thinking of just creating a batch file to copy the file to a new source directly with a task scheduler set up, but thought I'd check to see if anyone else had any better suggestions.

Ideally I'd like it to run daily and have the date of the back up added to the end of the filepath.

Any ideas?

 

[plog]

You got it--batch file run through task scheduler.

 

[kacey8]

Okay, any idea how I could add todays date onto the file name when using a batch file. I know how to do a simple copy with something like

copy /Y y:\[FILEPATH]\DB.ACCDB y:\[FILEPATH]\Backup\*.*

But obviously it'll use the default filename.

 

[plog]

Not offhand I don't.

 

[shadow9449]

The way I implement backups:

- I use Albert Kalall's method of zipping the database: http://www.kallal.ca/zip/index.htm This puts the date on the Zip file for you and moves it to wherever you want. Zipping also keeps the file size down.
- For split databases, I make the slight tweak of first finding the path to the back end. If that is known to you then not a problem. If you want to use the application to find the back end I use the following function (happens to be that I got this from Albert as well):

Function strBackEnd() As String

    Dim mytables        As dao.TableDef
    Dim strTempBack     As String
    Dim strFullPath     As String
    strFullPath = ""
    
    For Each mytables In CurrentDb.TableDefs
       If Left(mytables.Connect, 10) = ";DATABASE=" Then
          strFullPath = Mid(mytables.Connect, 11)
          Exit For
       End If
    Next mytables
    
    strBackEnd = strFullPath
    
End Function

-The final touch I use is to upload the zipped file to Dropbox. This is really easy because I just have to tell my program where the dropbox folder is and use that as the location for storage. It's then uploaded offsite and the free 2GB they give you is more than enough for me. There are similar services with more storage but this is plenty.

I put this all together and program my access applications to back up every time the user turns it on or off. You can probably integrate it with the task manager as well.

I hope this gives you some ideas.

SHADOW

 

[kacey8]

Shadow,

Thank you so much for that. I never checked back before doing a batch file myself.

For anyone who is curious I am using this code and it works perfectly.

@echo off
for /f "delims=" %%a in ('wmic OS Get localdatetime  ^| find "."') do set dt=%%a
set DD=%dt:~6,2%
set MM=%dt:~4,2%
set YYYY=%dt:~0,4%
set HH=%dt:~8,2%
set Min=%dt:~10,2%
set Sec=%dt:~12,2%

set stamp=%DD%-%MM%-%YYYY%_%HH%-%Min%-%Sec%

copy "X:\Test\copy.txt" "X:\Test\BE\copy - %stamp%.txt"