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:
-
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.
-
cd C:\Program Files\7-Zip
Changed to the 7-zip program directory
-
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
-
cd C:\DMP
Changed to the C:\DMP directory
-
del *.dmp
Deletes any DMP files that exist in the current working directory
-
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"