CODEWITHSUNDEEP
windowsbatch-file
Tj Keel
Tj Keel

Reputation: 89

Using a Batch file to automatically create a backup file with 5 running iterations

I'm looking for general advice on if this is possible/best way to go about it.

I have a split Access database. The back-end file is called data.mdb. In the same directory as data.mdb I want to place a .bat that will automatically create a copy of data.mdb every night. However, I don't want to create infinite backups. I just want a running list of the 5 most current. That is, after the 5th night, each subsequent backup should overwrite the oldest of the 5 versions of copy - data.mdb.

Is it possible to do this with a batch file, and could you give me examples about what the code would look like?

I'm assuming I'll have to alter windows settings somehow - maybe in msconfig - to make the .bat run automatically and at a specific time. Is there a way to do this within the .bat itself. My intuition says no, but that would be ideal.

If you think there is a better way to get the same result, I'd be interested in your thoughts. Thanks!

Upvotes: 1

Views: 2787

Answers (1)

Mofi
Mofi

Reputation: 49216

The following batch file could be used for this task:

@echo off
set "BackupFolder=Backup"
for /F "tokens=2 delims==" %%I in ('%SystemRoot%\System32\wbem\wmic.exe OS GET LocalDateTime /VALUE') do set "FileDate=%%I"
set "FileDate=%FileDate:~0,4%-%FileDate:~4,2%-%FileDate:~6,2%"
md "%BackupFolder%" 2>nul
copy /Y "data.mdb" "%BackupFolder%\data_%FileDate%.mdb" >nul
for /F "skip=5 delims=" %%I in ('dir /B /O-N "%BackupFolder%\data_*.mdb" 2^>nul') do del "%BackupFolder%\%%I"

The WMIC command outputs the current local date and time for example as:

LocalDateTime=20170410074206.062000+120

Just the string after the equal sign is of interest and assigned to variable FileDate by first FOR loop.

From this string just year, month and day is of interest for file name of backup file. Therefore the fourth command line uses string substitutions to get the local date in format YYYY-MM-DD. The string assigned finally to environment variable FileDate for the example is:

2017-04-10

A COPY is made of data.mdb into a backup folder always created if not already existing with an underscore and value of FileDate appended to file name. In your batch file the file data.mdb should be specified with full path and also the backup folder defined in second line should be specified with full path and not just with Backup. This is important later on running the batch file as scheduled task.

Then command DIR is executed to list all data_*.mdb files in backup directory in bare format because of /B sorted alphabetically in reverse order because of /O-N. The result is for example:

data_2017-04-10.mdb
data_2017-04-07.mdb
data_2017-04-06.mdb
data_2017-04-05.mdb
data_2017-04-04.mdb
data_2017-04-03.mdb

The first 5 lines which are the 5 newest files are skipped on processing this output by FOR loop. All other files are deleted which is usually only one file, the oldest file according to date in file name.

Configure in Windows task scheduler a scheduled task executing this batch file for example on Monday to Friday at 23:45 o'clock at night. By default the current directory on executing a batch file as scheduled task is %SystemRoot%\System32 and not the directory of the batch file which is the reason why it is recommended to specify backup folder and file data.mdb with full path in batch file.

It is important that the database application using data.mdb is not running while the batch file is executed as otherwise the file copy most likely fails because database applications usually open database files in exclusive mode, i.e. no other application can read/write the file while opened in database application.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

  • copy /?
  • del /?
  • echo /?
  • for /?
  • set /?
  • wmic /?
  • wmic OS /?
  • wmic OS GET /?

Read also the Microsoft article about Using Command Redirection Operators for an explanation of 2>nul used to suppress the error messages output by MD if backup folder already exists and of DIR if there is currently no file data_*.mdb in backup directory for example because of a manual deletion. The redirection operator > must be escaped in FOR command line with caret character ^ to be interpreted as literal character on parsing the FOR command line, but as redirection operator later on execution of DIR command by FOR in a separate command process in background.

Upvotes: 2

Related Questions