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 available databases:
- MSSQL databases in a single MSSQL server instance – in the instance specified by the -S parameter, otherwise in the first instance to which a connection can be established, in the order: instances found in the system registry, (local) and (local)\SQLEXPRESS; if there are more than three of these instances, the program first asks which instance to connect to,
- Firebird databases in the directories “E:\Firebird”, “D:\Firebird”, and “C:\Firebird”, and in the directory of the Firebird database specified in the “ConnectionString.txt” file in the “Config” directory of the NET Genium installation in which the program is located,
- 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; the instance name is written directly after the parameter, without a space. The parameter is needed whenever the required instance is not the one the program selects on its own:
- the instance has no entry in the system registry under the key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL” and is neither (local) nor (local)\SQLEXPRESS,
- there are at most three instances in the order given above and the required instance is not the first of them to which a connection can be established within one second – in that case the program does not ask for the instance,
- the program is run with the source parameter containing the name of an MSSQL database (typically a scheduled backup) – it then never asks for the instance, not even when there are more than three, and uses the first one to which a connection can be established.

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.