Depending on which database type you are running, the backup process will be different. Systemite offers a runtime backup option for SQLite with an option to back up the file repository whether it be a file-based repository or a database file repository. The type of file repository will somewhat affect the backup process. Regardless of which type of database you are running, if a file repository is in use, you will want to back it up regularly as well. This article describes how to back up an SQLite database as well as a file repository using the runtime backup option.


Recommendations

  • Whichever process you run, be sure to document your backup management procedures
  • Server must be up and running
  • Perform backups when there is no or little activity on the server
  • Backup routines should never be trusted without proper verification. Regularly assure that the backups can be restored, and that they really represent the intended database copy


The Runtime backup option for SQLite databases guarantees successful backup copies when users are logged in to the server. While the backup is performed, users will be able to browse SystemWeaver data, but any write operation will lock all clients until the backup operation has been completed. Therefore, it is advisable to schedule backups when there is no activity on the server. As always, since each environment is different, it is advisable that you complete a performance test prior.


A backup of a 20 GB SQLite database takes approximately 5 minutes if hardware requirements are met.


It is not recommended to use the Windows Copy (Ctrl+C) as a backup method.


Backup Using swServerCommand

An example of a backup method is provided in the SystemWeaver installation in the form of a backup.bat file and swServerCommand.exe. You will find the following backup.bat example file in the Configuration files directory. Or, you can copy the example below as a starting point.


Example backup.bat

swServerCommand backup ServerName=localhost ServerPort=1768
@echo off
rem swServerCommand backup ServerName=localhost ServerPort=1768 
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause


The swServerCommand.exe is located in the Server directory. This utility application uses a backup API call in the SystemWeaver server and is used as a scripting interface towards the swDBServer application. It uses the command line options to request specific server operations.  The script tells the server to create a copy of the database and place it in the location specified in the bat file. You can use a scheduler (Windows or similar) to run the script and take backups on a suitable schedule. 


The backup parameter specifies the backup operation.

The ServerName and ServerPort parameters identify the main server.

The FileName specifies the name of the database backup sqlite file.
If FileName is not specified, a default name will be given consisting of the name of the database filename_current date(YYYYMMDD)_a random number_backup. Example: "MyDatabase_20160921_172932_959_backup.sqlite". 

The file will be created in the folder where the SQLite database file is located.


Note: If the FileName is specified, but not the FileRepositoryFileName, the script will only perform a backup of the database. In other words, if you want to specify the FileName for the database backup, i.e., not use the default naming, you must also specify a FileRepositoryFileName for the file repository.


The FileRepositoryFileName specifies the name of the file repository backup. If no FileRepositoryFileName is specified, a default name is applied similar to the one described above for the database, but appended with "_backup.sqlite" or "_backup_files_index.db" depending on file repository type.


Examples

File-based: R36_20210217_171237_431_backup_files_index.db

Database file: R36_20210217_173034_776_backup.sqlite


File-Based Repository

This is the file repository format that we generally recommend. If a file-based file repository is used, the backup using swServerCommand will contain a backup of the index.db file only. It does not generate a backup of the files (data) in the repository so this must be done separately. So, it is a two-step process. To get a backup index file in sync with the file repository itself, it is recommended that the backup of the file repository files (data) be performed immediately after the backup of the index file.

  1. Backup of index.db file
  2. Backup of file repository data (files)


If not done as described above, a file repository backup from a later point of time will pose no real problem, other than that any files added to the repository after the backup index file was created will not be detected by the file index.


Database File Repository

If a database file repository is used,the single backup sqlite file will contain the entire file repository. 


Examples

Below are various example backup scripts. The example files to be backed up: 

  • Name of SQLite database: R36.sqlite
  • Name of SQLite file repository file: R36-FileRepository.sqlite
  • Directory for file-based repository: C:\Work\Database\FileRepository


SQLite Database Backup with no File Repository Using Default File Name

The backup will generate in the same location as the SQLite database file.

swServerCommand backup ServerName=localhost ServerPort=1768
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Result


SQLite Database Backup with no File Repository with Database Backup Filename/Location Specified

swServerCommand backup ServerName=localhost ServerPort=1768 FileName="C:\Backups\DBbackup.sqlite"
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Result


SQLite Database Backup with File Repository Backup Using Default File Names

The backup will generate in the same location as the SQLite database file.

swServerCommand backup ServerName=localhost ServerPort=1768
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Result for file-based repository

Note: The file data must be backed up separately. 


Result for database file repository


SQLite Database Backup and File Repository Backup with Database and File Repository Backup Filenames/Locations Specified


Database File Repository

swServerCommand backup ServerName=localhost ServerPort=1768 FileName="C:\Backups\DBbackup.sqlite" FileRepositoryFileName="C:\Backups\FileRepos_backup.sqlite"
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Note that the FileName and FileRepositoryFileName parameters should be located on the same line.


Result


File-based File Repository
swServerCommand backup ServerName=localhost ServerPort=1768 FileName="C:\Backups\DBbackup.sqlite" FileRepositoryFileName="C:\Backups\FileRepos_backup_index.db"
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Result

File Repository Backup Only

If you are using a MS SQL or Oracle database, refer to the documentation from the supplier of the components you are using for proper backup methods. Since the file repository in SystemWeaver is separate from the database, the backup of it is not part of the MS SQL or Oracle backup methods. It can be performed using the runtime backup option described above, however you would only be backing up the file repository. Therefore, FileName can be left blank. 


Database File Repository

swServerCommand backup ServerName=localhost ServerPort=1768 FileRepositoryFileName="C:\Backups\FileRepos_backup.sqlite"
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Result


File-based File Repository 

swServerCommand backup ServerName=localhost ServerPort=1768 FileRepositoryFileName="C:\Backups\FileRepos_backup_index.db"
@echo off
if %ERRORLEVEL% NEQ 0 (
  echo Backup command returned error
  pause
  exit
)

pause

Result



If a script runs successfully, the result in the command window will appear something like the below example:


Backup Using swTestServer Utility

The above backup option is also available as a button in the swTestServer. Note that the naming of the output is the default naming described above. The backup will generate in the same location as the SQLite database file. The log file will indicate when the backup is done. Do not close the tool until you get this confirmation. 



Side Note on MS Windows Backup Utility

The Backup utility in MS Windows will not perform backup of locked files, including database files. This means that the database of a running SystemWeaver server, using a file-based SQLite database, will actually never be backed up by MS Windows. The solution is to first stop the server (see Stopping and starting a SystemWeaver server). An alternative to stopping the server is to make a copy of the file first and then perform the backup on the copy. Since there is always a risk of a concurrent database write transaction, the copied file may become corrupted. To minimize this risk, always perform the backup when there are few users (which may be difficult in a 24/7 situation) typically at night or on a weekend. Backup routines should never be trusted without proper verification. The Administrator should therefore regularly assure that the backups can be restored and that they really represent the intended database copy.


Compaction of Large SQLite Files

The swDatabaseManager.exe includes an option called Vacuum SQLite database which will minimize the SQLite database file. See Compacting Large SQLite Files for more information.


Tip: Always make sure that the storage used for database backups is sufficient.


Note: The .swjournal file used in the mirror server solution is not included in the described runtime backup option for SQLite. The purpose of the database backup is to create a copy of data that can be recovered in the event of a failure. The content of the swjournal file is already in the database. In the event that you have to revert to a database file backup, you could generate a new swjournal file using the swDatabaseManager, or use the existing one if it still accessible.