Backup Media with Database – RAR #batch #backup #media #rar

(See Backup Media with Database – 7Zip for a similar procedure using 7-zip.)

Here is a one-click backup procedure that stores the database file and all the media files it references in one solid, compressed backup file, preserving the path to each. Many have lamented the fact that RootsMagic 4 neither stores images in its database file nor backs up media files together with the database file so that all the files can be transported together between drives, computers, cloud storage, etc. A workaround is to follow the procedure Create a Shareable CD and zip the collected files found in its temporary folder prior to burning the CD but this has proven cumbersome, slow, the folder hierachy is collapsed and multiple links to the same media file result in as many copies.

The Zip encoder RM4-7 uses for Backup may not be able to handle a large database with many media files – another reason to use outboard procedures until its limitations are lifted. See this article in Wikipedia on the early Zip limits.

This procedure is based on a very simple query that lists the paths and names of all the media files referenced by the database:

SELECT DISTINCT MediaPath || MediaFile FROM MultiMediaTable;

The following command line procedure or batch file runs the command line version of SQLite3, opening the RootsMagic database whose name is passed in the call to the procedure and executes the query. The resulting list of media files is passed via STDIN to RAR which creates or updates the backup file. When RAR finishes the list from sqlite, it sees the database filename and processes it, too. The resulting .rar file can be opened by WinRAR and a variety of other archiving utilities. The procedure could be revised to use a different archiver.

REM RMfullbackup.bat
REM by Tom Holden 2011-02-02
REM Backs up a RootsMagic database file and all the media files referenced by it to one, compressed RAR file.
REM Command syntax: RMfullbackup.bat <databasename>
REM Close the database file before running.
REM The first backup can take considerable time, depending on the total of the file sizes.
REM To save time on subsequent backups, RMfullbackup merely updates the backup file for those files added, changed or deleted.
REM Install the SQLite3 command line version in the same directory as the RMGC database file(s)
REM  OR set a system PATH to where sqlite3.exe is located
REM  OR prepend the path to sqlite3.exe in the command below.
REM Likewise for RAR.exe
REM Under the RM Data folder, create a folder Backups; if a different path is to be used, edit the command line below.
REM The backup file will be written to the Backups folder and will have the name <databasename>_fullbackup.rar
REM An error log will also be written to the Backups folder with the name <databasename>_fullbackuperror.log
REM backup media files listed by query followed by the database file
sqlite3.exe %1.rmgc "SELECT DISTINCT MediaPath || MediaFile from MultiMediaTable;" | rar a -u -as -ilog.backups%1_fullbackuperror.log backups%1_fullbackup @ %1.rmgc

Download and save in the folder where you have your RootsMagic databases:
RMfullbackup.bat.bak (.bak added due some systems’ security. Remove after d/l)

Create a shortcut to RMfullbackup.bat <databasename> and name and place it where it is convenient. Do not include “.rmgc” in the database name. Make as many of these shortcuts as there are different databases to be backed up.

Clicking on the shortcut will launch a command window where you will see RAR working through the list. When it is finished the window will stay open until you press a key, allowing an opportunity for you to inspect the messages. Error messages from RAR are stored in the error log for later review. Note that the RM database must be closed in order for the database file to be processed by RAR.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.