Windows Scripting: A sample batch file to backup and compact Access Databases

Hi All,

As a novice windows batch script writer I appoligize you first for posting such a silly post. Here I have learnt some thing like

1. How to copy file over the network with file name containing spaces (with double quote stuff).
2. How to create a logfile against a batch job.
3. How to remove n days older files (with forfiles command).
4. How to format date parameter for filename suffix.
5. How to compact and repair access databases.
6. How to use if condition and Label.

::::::::::::: Create a log file :::::::::::::::
setlocal
set "LogPath=D:\temp\accessdb_backup\logs\"
set LogFileExt=.log
set LogFileName=DailyAccessDBBackup%LogFileExt%
::use set MyLogFile=%date:~4% instead to remove the day of the week
set MyLogFile=%date:~10,4%%date:~4,2%%date:~7,2%::set MyLogFile=%MyLogFile:/=-%
set MyLogFile=%LogPath%%MyLogFile%_%LogFileName%
::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space
If NOT Exist "%MyLogFile%" goto:noseparatorEcho.>>"%MyLogFile%"
Echo.===================>>"%MyLogFile%"
:noseparator
echo Job Starts on %Date% %Time% >>"%MyLogFile%"

:::::::::::::: Original Tasks start here :::::::::::
set "destpath=D:\temp\accessdb_backup\"
set n=1
Rem First Print the file name n day older
Forfiles -p "%destpath%" -s -m *.mdb -d %n% -c "cmd /C echo @File" >>"%MyLogFile%"
Forfiles -p "%destpath%" -s -m *.accdb -d %n% -c "cmd /C echo @File" >>"%MyLogFile%"
Rem Now Delete files 1 day older
Forfiles -p "%destpath%" -s -m *.mdb -d %n% -c "cmd /C del /q @path" >>"%MyLogFile%"
Forfiles -p "%destpath%" -s -m *.accdb -d %n% -c "cmd /C del /q @path" >>"%MyLogFile%"

Rem Delete older directories upon value of above n
PushD "%path%" &&("C:\WINDOWS\system32\forfiles.exe" /D %n% /C "CMD /C if @ISDIR==TRUE echo RD /Q @FILE &RD /Q /S @FILE") & PopD >>"%MyLogFile%" 

Rem Delete older directories upon value of above n
PushD "%path%" &&("C:\WINDOWS\system32\forfiles.exe" /D %n% /C "CMD /C if @ISDIR==TRUE echo RD /Q @FILE &RD /Q /S @FILE") & PopD >>"%MyLogFile%"

Rem Command to Copy the files and Rename with date suffix:
::set the source db file name with path
::set "db1=C:\Temp\Database1.accdb"
::set "db2=C:\Temp\Database7.accdb"
::set "db3=C:\Temp\Database1.mdb"
::set "db4=C:\Temp\Database2.mdb"
set "db4=\\servername\Databases\database4.mdb"
set "db5=\\servername\Databases\database5.mdb"
Rem format date field
::::::::::::::::::::::::
set dt=%date:~10,4%%date:~4,2%%date:~7,2%
Rem set the destination db file name only not the path
::::::::::::::::::::::::::::::::::::::::::::::::::::::
set "dest_db1=Database1_%dt%.accdb"
set "dest_db2=Database7_%dt%.accdb"
set "dest_db3=Database1_%dt%.mdb"
set "dest_db4=database4_%dt%.mdb"
set "dest_db5=database5_%dt%.mdb"
Rem copy the source db to destination db
::::::::::::::::::::::::::::::::::::::::::
::echo f|C:\Windows\System32\xcopy.exe /Y %db1% %destpath%%dest_db1%
::echo f|C:\Windows\System32\xcopy.exe /Y %db2% %destpath%%dest_db2%
::echo f|C:\Windows\System32\xcopy.exe /Y %db3% %destpath%%dest_db3%
echo "copy started at "%Time% >>"%MyLogFile%"
echo f|C:\Windows\System32\xcopy.exe /Y "%db4%" "%destpath%%dest_db4%" >>"%MyLogFile%"
echo "copy started at "%Time% >>"%MyLogFile%"
echo f|C:\Windows\System32\xcopy.exe /Y "%db5%" "%destpath%%dest_db5%" >>"%MyLogFile%"

Rem Command to Compact and Repair:
::::::::::::::::::::::::::::::::::::
cd C:\Program Files\Microsoft Office\OFFICE11
::MSACCESS.EXE "%destpath%%dest_db1%" /compact
::MSACCESS.EXE "%destpath%%dest_db2%" /compact
::MSACCESS.EXE "%destpath%%dest_db3%" /compact
echo "Compact and Repair Starts at "%Time%" of ""%destpath%%dest_db4%" >>"%MyLogFile%"
MSACCESS.EXE "%destpath%%dest_db4%" /excl /compact >>"%MyLogFile%"
echo "Compact and Repair Starts at "%Time%" of ""%destpath%%dest_db5%" >>"%MyLogFile%"
MSACCESS.EXE "%destpath%%dest_db5%" /excl /compact >>"%MyLogFile%"

echo "Job Finished on "%Date%" "%Time% >>"%MyLogFile%"

Another code snippet to check if any user logged into the Access Database or not. If yes then the database will not be compacted, it will skipped to the next database. The checking is done by the existence of *.ldb file.

    ::::::::::::: Create a log file :::::::::::::::
    setlocal
    set "LogPath=C:\temp\accessdb_backup\logs\"
    set LogFileExt=.log
    set LogFileName=compact%LogFileExt%
    ::use set MyLogFile=%date:~4% instead to remove the day of the week
    set MyLogFile=%date:~10,4%%date:~4,2%%date:~7,2%
    ::set MyLogFile=%MyLogFile:/=-%
    set MyLogFile=%LogPath%%MyLogFile%_%LogFileName%
    ::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space
    If NOT Exist "%MyLogFile%" goto:noseparator Echo.>>"%MyLogFile%"
    Echo.===================>>"%MyLogFile%"
    :noseparator
    echo Job Starts on %Date% %Time% >>"%MyLogFile%"


   
    :::::::::::::: Original Tasks start here :::::::::::
   
    set "db1=C:\Users\Saifur Shaon\Documents\accessDB\Database1.mdb"
    set "log_db1=C:\Users\Saifur Shaon\Documents\accessDB\Database1.ldb"
    set "db2=C:\Users\Saifur Shaon\Documents\accessDB\Database2.mdb"
    set "log_db2=C:\Users\Saifur Shaon\Documents\accessDB\Database2.ldb"
           
    IF EXIST "%log_db1%" (
        echo "%db1%" is in use!!! >>"%MyLogFile%" && echo starting to compact next db >>"%MyLogFile%" && goto :lbl_log_db2
    )   
    echo Compact and Repair Starts at %Time% of "%db1%" >>"%MyLogFile%"
    "C:\Program Files\Microsoft Office\OFFICE14\MSACCESS.EXE" "%db1%" /compact
    echo Compact and Repair Finished at %Time% of "%db1%" >>"%MyLogFile%"

    :lbl_log_db2
    IF EXIST "%log_db2%" (
        echo "%db2%" is in use!!! >>"%MyLogFile%" && goto :job_end
    )
    echo Compact and Repair Starts at %Time% of "%db2%" >>"%MyLogFile%"
    "C:\Program Files\Microsoft Office\OFFICE14\MSACCESS.EXE" "%db2%" /compact
    echo Compact and Repair Finished at %Time% of "%db2%" >>"%MyLogFile%"
   
    :job_end    echo Job Finished on %Date% %Time% >>"%MyLogFile%"