Backing up databases

Backing up is the most important task in database administration. Backups allow you to retrieve your data when a server is down, or when configurations, data files, etc. are lost.

We recommend that you schedule automatic backups of your databases so that they are carried out regularly. For more information on scheduling, refer to Scheduling maintenance via SQL Agent.

To mitigate the risks, you can also back up the databases before important operations, such as:

  • Before updating SES Evolution: If you are unable to back up the entire physical or virtual machine, you can make a backup of both databases so that you can reinstall the product and restore the databases in the event of a serious incident during the update.

  • After updating SES Evolution: Performing a backup immediately after the product update enables restoration in the event of an incident occurring between the end of the update and the next scheduled backup. This avoids having to reinstall the previous version of SES Evolution, restoring the previous backup, and finally repeating the update.

To back up the databases:

  1. Create the destination directories containing the backups of the log and administration databases, for example E:\Backups\EsAdministration et E:\Backups\EsLogs.

  2. Ensure that SQL Server has write privileges for these directories. The SQL Server execution account is in the form MSSQL$ENDPOINTSECURITY if your instance is named ENDPOINTSECURITY.

  3. In SQL Server Management Studio, call the stored procedure Stormshield_BackupDatabase and provide the following parameters, specific to your environment:

Parameter Description
DatabaseName

Name of the database to be backed up.

The value can be EsAdministration or EsLogs.

BackupDirectory

Absolute path of the directory in which the backup file is created.

This directory must exist.

Network paths are accepted, for example: \\storage\backups\EsAdministration.

The file created is in the format DATABASENAME_YYYY-MM-DD_HH-MM-SS_TYPE.bak.

For example: EsAdministration_2024-07-14_22-30-42_full.bak or EsLogs_2024-07-14_22-30-42_diff.bak.
BackupType

Type of backup to create.

The value can be:

  • 'full' for a full backup of the database to the backup file,

  • 'diff' for a differential backup. The backup file contains only those data that have changed since the last full backup.

    The file size depends on the use of the database. Usually, a differential backup file is much smaller than a full backup file. Differential backups can be produced more frequently as they are faster and generate a smaller file. However, restoring is more complex.
Compress

Enables or disables compression during the backup. Compression produces a smaller file with a slightly higher CPU consumption.

The value can be:

  • 1: Compression enabled (default and recommended value),

  • 0: Compression disabled.
CheckSum

Enables or disables the creation of data integrity checksums. These checksums increase the resilience of the backup files in terms of corruption.

The value can be:

  • 1: Creation enabled (default and recommended value),

  • 0: Creation disabled.
Verify

Enables or disables backup file verification once the backup operation is complete. If verify is enabled, SQL Server checks the backup file (e.g., structure, integrity, checksum if enabled). This verification extends the operation's duration, but allows early detection of errors in the backup file.

  • 1: Verification enabled (default and recommended value),

  • 0: Verification disabled.
DryRun

Enables or disables running the procedure in test mode. when the value is 1, the procedure does not actually run the commands and merely displays them. This allows the procedure to be tested before running it in a real-life situation.

  • 1: Test mode enabled,

  • 0: Test mode disabled (default value).
CopyOnly

Enables or disables the ability to create a backup that will not be recorded in the log of database backups. This can be useful when exporting the database.

  • 1: Backup absent from history enabled,

  • 0: Backup absent from history disabled (default value).

Example commands for a complete point-in-time backup of both databases:

EXECUTE master.dbo.Stormshield_BackupDatabase
@DatabaseName = 'EsAdministration',
@BackupDirectory = 'E:\Backups\EsAdministration',
@BackupType = 'full';

EXECUTE master.dbo.Stormshield_BackupDatabase
@DatabaseName = 'EsLogs',
@BackupDirectory = 'E:\Backups\EsLogs',
@BackupType = 'full';

NOTE
To comply with best practices and avoid potential deletion errors, the procedure does not delete existing backup files. To recover space on your backup medium, implement a policy for deleting obsolete backup files meeting your data preservation needs.