Backup database open in both SQLite and RootsMagic #backup

Problem

Neither the RootsMagic File>Backup nor File>Copy can proceed because the database file “is being used by another process” (RootsMagic’s own or your SQLite manager or other outboard SQLite connection). And if you’ve chosen Backup, RM gracelessly blocks you from further work with an always-on-top fake news message window “Creating backup file…” requiring closure of RM to clear.

If you were using a SQLite manager that’s connected to the database file, you have to close it in order to use RM’s utilities and then reopen it with numerous steps to finally execute the SQL you’ve been carefully crafting to make some important changes to your data. That disruption takes time, may lose some valuable history from the SQLite manager and, perhaps, derail your train of thought. Same goes for ordinary users getting thrown off track after running Compact and other unknown things that screw up the RM backup.

Outboard zip utilities such as 7Zip, RAR, WinZip,… all, to the best of my knowledge, require the closure of all other processes for them to proceed but do handle such a blockage gracefully, unlike RM4-7. It’s not sufficient that RM get to handle the blockage gracefully; it shouldn’t be blocked at all!

Some Workarounds

Looking into this issue in 2019, I’ve discovered some useful alternatives to RM’s backup and copy utilities that have arisen since the days of RM4 and Windows XP in 2009/10 when the original SQLite Tools for RootsMagic 4 Wiki began. SQLite Expert Professional lists “Backup” as a feature that likely uses the Online Backup API, perhaps the best suited to the task but at a price.

This post presents four free, fast backup methods that allow the database to remain open in RootsMagic and in one (or more) outboard SQLite connections. They have been tested with a database open in RM7.5.9 and simultaneously in three SQLite managers: SQLite Expert Personal 4.2.0.784, SQLiteSpy 1.9.12 and SQLiteStudio  3.2.1.

1. Windows 10 File Explorer
2. SQLite Command: VACUUM INTO
3. SQLite Archive File
4. Export to SQL Text file

Windows 10 File Explorer

My first surprise was to discover that the Zip function integrated in File Explorer (possibly from even before Win10) does not object to the database file being open. Nor does its copy function. The latter may be the easiest and fastest method.

Copy

With the File Explorer window open and the “filename.rmgc” database file selected, Copy (Ctrl+C) and Paste (Ctrl+V) results in “filename – Copy.rmgc”. Repeat and the copy is automatically serialized as

“filename – Copy (2).rmgc”
“filename – Copy (3).rmgc”
…

No manual file naming is needed to prevent overwriting of the last copy and any copy can be immediately opened by RM or any of the SQLite apps. The Created Date timestamp for the copy is the Date Modified timestamp of the original, so its value puts the copy’s version of the data into context, provided you have some log (recorded or remembered) of what you did before and after.

Zip

Right-click on the selected “filename.rmgc” database file and, from the popup context menu, click on Send to > Compressed (zipped) folder. This will popup a dialog box to allow you to change the default name “filename.zip”.

Repeat and File Explorer automatically serializes the name:
“filename (2).zip”
“filename (3).zip”
…

You could change the zip filename extension to “.rmgb” at any of these backups so that RootsMagic could find and restore from it but I wouldn’t bother for two reasons:

1. It interferes with serializing the filename to preserve successive backups.
2. It’s easy to extract from the Zip folder using File Explorer which gives you great control over where the extracted file goes with opportunity to rename and move it afterward.

Using File Explorer from within the apps

All of the mentioned apps launch a basic Windows File Explorer dialog Window to Open a database or carry out some other operation. All support Copy, Paste and Send to Compressed (zipped) Folder. Because RM’s window filters for .rmgc files, it is quicker selecting the database file. Presumably, the speed of the subsequent operation is independent of the application that opened the File Explorer dialog. When the copy of zip is complete, simply Cancel the window to back out to where you were with the database.

Copying or Zipping from the Command-Line or PowerShell

If File Explorer could copy or zip the database file while open in RootsMagic and SQLite, it seemed reasonable that these other Windows utilities could, too. The Zip is only available in PowerShell but both can copy files. Unfortunately, both the CLI and PS detected another process and refused to proceed.

SQLite command: VACUUM INTO

The VACUUM command was provided in SQLite 3 and all of the referenced SQLite managers from the get-go but it operated directly on the database in memory. About two months ago (ver 3.27), the command was extended with an INTO clause that writes the result of the VACUUM to a new database file without modifying the original. Effectively, this means that one can make a vacuumed copy of the currently opened database from a SQLite manager as a backup. If the original has not been recently vacuumed by either RootsMagic or by an outboard SQLite manager, then the vacuumed copy will be smaller in size.

The copy can be arbitrarily named and, if named with the .rmgc extension, can be opened directly by RootsMagic without going through a Restore process.

Typing the command and filename and ensuring the latter is unique is less efficient than the File Explorer method. A potential productivity gain would be to write a Windows command or Powershell script to launch the current sqlite3.exe command line shell program to open the database file to VACUUM INTO a filename defined by the script; it could incorporate date and time in the filename. Alternatively, the batch or command script could also zip the resulting .rmgc file and give the zip file the unique name.

After some time, the referenced SQLite managers will undoubtedly update to SQLite 3.27 to support the command and some may incorporate some higher level controls such as a file explorer dialog to facilitate the naming of the vacuumed file.

This command should work in SQLite for both Windows and MacOS.

For the SQLite 3 command line shell program, look for sqlite-tools-win32-x86-3270200.zip or later at https://www.sqlite.org/download.html. Also available for MacOS: sqlite-tools-osx-x86-3270200.zip.

SQLite Archive File

Since 2014, there has been development of a SQLite Archiver utility “sqlar” using SQLite and zlib to archive files of any type to a SQLite database container. The file contents are zipped to a Blob-type field in a table with one row for each archived file. In 2018, it has been made into an optional extension to the SQLite source code and is now integrated in the SQLite command-line shell tool mentioned above. It is possible for a SQLite manager to be compiled with this extension but I don’t know of any yet.

The SQLite command-line tool from 3.22 can be opened and at its prompt given the “.ar” command to  create a SQLite Archive file, add|update files to it, list contents, and extract files from it. From 3.23, the recommended use is from the OS CLI to call sqlite3.exe with the -A option.

In this example in sqlite3.exe, I updated the SQLite Archive file “_LGS_pres.db” with the database “_LGS_pres.rmgc” that was open in RM and the 3 SQLite managers.

sqlite> .ar -uvf _LGS_pres.db _LGS_pres.rmgc
_LGS_pres.rmgc

The Archive file could contain many other files, e.g., the media files or other unrelated files, just as a Zip folder can.

Moreover, the SQLite Archiver can operate as a zip utility producing a .zip file (“compressed folder” in Win10) instead of a SQLite Archive file, skipping over the locks that trip up RootsMagic’s backup and 7Zip.

For more, see SQLite Archive Files.

Export to SQL Text file

This is an old method that has merit because it reads the SQLite database, not just copying it, to create SQL commands which can later be executed to build the identical database. Some SQLite managers support it (SQLiteStudio) and it is available in the command-line shell:

Use the “.dump” command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

The resulting text file is bigger than the database file but it can be piped into a zip utility for compression. Whether there is a limit that a large database might run into remains to be seen.

For more, see Converting An Entire Database To An ASCII Text File

Observations

1. There are multiple ways to backup a RootsMagic database while it is open in both the RootsMagic application and one or more outboard SQLite connections.
2. Using these other ways instead of the RootsMagic backup function may also avoid the recurring situation when RootsMagic blocks itself from completing a backup; a forced shutdown is the only way to resume working in RootsMagic.
3. For the less technically proficient, Windows File Explorer provides a convenient alternative to the RM backup, even from within the RootsMagic user interface.
4. RootsMagic developers should look into some of these alternatives that work with SQLite to resolve the conflict with outboard SQLite connections. Especially appropriate could be the Online Backup API because it is native to SQLite and aware of other users of the database, allowing them to “continue uninterrupted while a backup of an online database is made”.

Caveats

1. Testing these has been necessarily superficial so there may be issues as yet undetected. Please comment below if you come across any.
2. Make sure that the database is not being modified by one of the connections while the ‘backup’ is going on. When the backup is opened, there could be unexpected results. This is especially true of those methods that work without a SQLite connection to the database, i.e., File Explorer, SQLite Archiver.
3. Those methods that require a SQLite connection to the database, i.e., VACUUM INTO, Export and Online Backup API, need the RMNOCASE collation (see RMNOCASE; faking it in SQLite Expert, command-line shell et al). It is possible that the resulting database will fail the RootsMagic database integrity test; simply use its Rebuild Indexes tool to correct.