Database backup and restore

1. Database backup

Database backups are performed using the program “SqlBackup.exe”, which is located in the “Backup” directory of each NET Genium installation. The program supports backups of both MSSQL and Firebird databases.
Although databases can generally be backed up either fully or incrementally, the program “SqlBackup.exe” supports **full database backups only**.
The program “SqlBackup.exe” can be executed only on the computer where the database server is running and where the database is physically stored.

Usage:

SqlBackup.exe [source] [target]

The source parameter specifies the database name (for an MSSQL database) or the full path to the database file on disk (for a Firebird database).
The target parameter specifies the file name or full path of the file in which the backup will be stored. The file name may contain the variables #year#, #month#, #day#, #dayofweek#, #dayofyear#, and #week#.

Example:

SqlBackup.exe netgenium netgenium-#dayofweek#

Using variables in the backup file name is useful not only for identifying the backed-up database, but also for controlling the number of retained backups. The program “SqlBackup.exe” overwrites files with the same name. For example, the #dayofweek# variable can be used to maintain a weekly rotation of backups (files netgenium-1 through netgenium-7). When a new backup is created, the older file with the same name is automatically overwritten.
If the target parameter is not specified (only the database name or its full path is provided), the backup file name is generated in the format “yyyy-mm-dd-database_name”, and the backup file is stored in the directory from which “SqlBackup.exe” is executed.
When backing up an MSSQL database, the optional parameter “/backuplog” can be used to also back up the database transaction log. The transaction log backup is stored in a separate file named in the format “database_backup_name-log.bak” and in the same location as the database backup.
After a successful backup, a service named “SqlBackup” is automatically logged into the database table “ng_sluzbawindows” with an interval of 1 day.

When the program “SqlBackup.exe” is started without parameters, it offers several options depending on the required operation:

The B option performs a database backup. It is important to note that MSSQL and Firebird databases are handled differently. An MSSQL database is identified by its database name and the MSSQL server instance, while a Firebird database is identified by the physical path to the database file. MSSQL backups use the “.bak” extension, while Firebird backups use the “.fbk” extension.

When the B option is selected, the program “SqlBackup.exe” searches for all available databases:
- in the default MSSQL server instances (local) and (local)\SQLEXPRESS,
- in other MSSQL server instances found in the system registry,
- in the directories “E:\Firebird”, “D:\Firebird”, and “C:\Firebird”,
- and then prompts the user to select the database to be backed up.

The optional parameter -S can be used to explicitly specify an MSSQL server instance. This is useful if the required instance is not detected automatically, for example if it does not have a registry entry or uses a non-standard name.

Example:

SqlBackup.exe -S(local)\SQLEXPRESS

The BTHIS or BT options back up the database specified in the file “ConnectionString.txt”, which is located in the “Config” directory.

The program “SqlBackup.exe” is a console application and can therefore be executed using a batch file. This batch file can then be scheduled to run automatically using the Windows Task Scheduler.
The batch file must use absolute paths both to the “SqlBackup.exe” program and to the target backup file. If the target file is not specified with a full path, the backup will be stored in the “C:\Windows\System32” directory.

To schedule a database backup, create a batch file, for example “BackupDatabase.bat”, and save it in the NET Genium installation directory.

C:\inetpub\wwwroot\NETGenium\Backup\SqlBackup.exe netgenium C:\inetpub\wwwroot\NETGenium\Backup\netgenium-#dayofweek#

C:\inetpub\wwwroot\NETGenium\Backup\SqlBackup.exe C:\Firebird\netgenium.fdb C:\inetpub\wwwroot\NETGenium\Backup\netgenium-#dayofweek#

C:\inetpub\wwwroot\NETGenium\Backup\SqlBackup.exe netgenium C:\inetpub\wwwroot\NETGenium\Backup\netgenium-#day#

C:\inetpub\wwwroot\NETGenium\Backup\SqlBackup.exe C:\Firebird\netgenium.fdb C:\inetpub\wwwroot\NETGenium\Backup\netgenium-#day#

C:\inetpub\wwwroot\NETGenium\Backup\SqlBackup.exe netgenium C:\inetpub\wwwroot\NETGenium\Backup\netgenium-#month#-#day#

C:\inetpub\wwwroot\NETGenium\Backup\SqlBackup.exe C:\Firebird\netgenium.fdb C:\inetpub\wwwroot\NETGenium\Backup\netgenium-#month#-#day#

Open the Task Scheduler.
Click “Create Task...”.
On the “General” tab, enter the task name and configure the user account under which the task will run. Enable the option “Run with highest privileges” to ensure the backup can be completed successfully.
On the “Triggers” tab, click “New” and configure the time and frequency of the backup.
On the “Actions” tab, click “New” and configure the execution of the batch file “BackupDatabase.bat”.

To restore a database, start the program “SqlBackup.exe” and select the R option (restore).
The program searches for all available backups in the directory from which it is executed and prompts the user to select the backup to restore. Restoring to an existing database will overwrite it.
After selecting a backup, you will be prompted to enter the name of the database into which the backup should be restored.
For security reasons, you will then be asked to confirm the entered database name.
At the end of the restore process, you will be asked whether the database should be restored to the default directory of the MSSQL or Firebird database server.