Skip to content

Creating a daily database backup

  • Requirements
    • An file archive program (7-zip, WinZip, WinRAR) with CLI capabilities
    • Lode Database Utilities
  • Procedure
    • Setup file archive program
    • Create BAT script for database backup
    • Create daily Task

Requirements

Install a file archive program such as 7-zip, WinZip, or WinRAR. The program you select should have a command line interface. In this documentation we use the 7-zip program as an example.

Install The Lode Database Utilities program (version 10.20). This program will create an Oracle Database Dump File (DMP).

Procedure

  • Create a directory to save the archives
  • Create a file list for your archive program
  • Create a BAT file
  • Create a daily task to create the backup archive

Create backup directory

First you need to create a directory to save the daily backup to. In this example we will work out of the C:\DMP directory on the Oracle Database server.

Open a CMD prompt

Create a directory on the C drive named DMP

CD C:\ 
mkdir C:\DMP

Create file list

We will be working with 7-zip to archive our files and specify the files we want to include in our archive using a file list.

Navigate to the directory where you installed 7-zip. The default install directory on Windows is located at C:\Program Files\7-zip.

Create a new text file named lode_backup_files.txt

C:\Program Files\7-zip\lode_backup_files.txt

In the text file, list the files you want to include in the archive.

Example:

C:\DMP\*.dmp
C:\DMP\export.log

In the example we are telling 7-zip to archive any file with the DMP file extension and our export.log

Create BAT file

Open Notepad or your favorite text editor

In the file place the following lines:

  1. START /d "C:\Program Files\Lode Data Corporation\Database Utilities" /B /wait FiberExport.exe {database name} {SYS password} {System password} {Schema} C:\DMP {database name}\_%date:~-4,4%_%date:~-10,2%_%date:~-7,2%.dmp

    Tip

    The { } are placeholder for arguments we are passing to the FiberExport.exe program. Replace the { } with your arguments and remove the { }. FiberExport.exe {database name} {SYS password} {System password} {Schema} C:\DMP {database name}_current date.dmp

    This will start the Lode Database Utilities program.

  2. cd C:\Program Files\7-Zip

    Changed to the 7-zip program directory

  3. 7z a -t7z C:\DMP\\{database name}\_%date:~-4,4%_%date:~-10,2%_%date:~-7,2%.7z @lode_backup_files.txt

    Creates a 7z archive in the C:\DMP directory with your database name and the current date using the lode_backup_file.txt list

  4. cd C:\DMP

    Changed to the C:\DMP directory

  5. del *.dmp

    Deletes any DMP files that exist in the current working directory

  6. del *.log

    Deletes any log files that exist in the current working directory

Example:

This uses a database named lode with all user passwords = lode and a schema = data following the FiberExport.exe command.

START /d "C:\Program Files\Lode Data Corporation\Database Utilities" /B /wait FiberExport.exe lode lode lode data C:\DMP lode_%date:~-4,4%_%date:~-10,2%_%date:~-7,2%.dmp
cd C:\Program Files\7-Zip
7z a -t7z C:\DMP\LODE_%date:~-4,4%_%date:~-10,2%_%date:~-7,2%.7z @lode_backup_file_list.txt
cd C:\DMP
del *.dmp
del *.log

Tip

If it is still unclear about the arguments that are passed on line number 1, it may be useful to open a run the FiberExport.exe program. You'll then see the prompts for the arguments we are passing.

Save the file as a BAT file such as LodeBackup.BAT in the C:\DMP directory

C:\DMP\LodeBackup.BAT

Schedule a Windows Task

Schedule a Windows Task to be ran daily at your preferred time using the BAT file you created.

You will now have a daily task that will create a DMP file using the Lode Data Utilities program. Once the DMP file is created the BAT file will archive and compress the file using 7-zip. After the files are archive the BAT file will clean up the left over DMP file and log files.

Tip

Keep in mind that if you have a large database your C: drive may get full quickly. You can add a line to your BAT file to move your archives to another drive or to a network share.

Example:

move /-y "C:\DMP\*.7z" "E:\Backup DMP"