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.

• Backup Using swServerCommand
• Backup Using swTestServer Utility

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 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.

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

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

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

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.