Database Backups

Patriot can back up the entire SQL database into standard SQL backup files, for easy recovery from hardware failure or operator error. Routine backups should be an essential part of monitoring, and Patriot strongly recommends making additional backups to removable storage devices, and storing them in a secure location off-site. If backups are not enabled, a warning will be displayed in the system status icon.

Database backups should be made and stored at an interval shorter than the maximum age of your Patriot database(s) setup in Archiving & Pruning Settings

There are two types of backup which can be performed:

• Database / Full Backup

  Backs up the actual data files.

• Transaction Log

  Backs up the transaction logs. Transaction Logs store all changes made to the database since the transaction log was last backed up. They allow the database to be rolled back to a point in time if required.

note

It is most important that the transaction logs are backed up regularly, otherwise they will continue to grow and eventually full the entire hard drive.

Automated Backup Setup

The backup settings can be accessed from System > Database > Backup Settings. From this screen, automated backups can be configured and checked. The available settings for the automated backups can be seen below:

Backup Settings Screen

Backup Folder

Determines the folder where the backups will be stored. Please note that although backups can be set up from any Patriot workstation, the folder selected will always be relative to the SQL server (normally the Patriot server). For example, a backup path of C:\ will store the backups on the root hard drive of the SQL server.

Full Backup Frequency

Determines whether the automated full backups are run on a daily or weekly basis. If run on a weekly basis, the Run Backup On option will appear allowing the day of the week to be chosen.

Full Backup Time Of Day

Determines the time of day that the full backup will be taken. Patriot recommends that this is scheduled during a quiet time for the monitoring station, such as very early in the morning or late at night.

Log Backup Frequency

Determines whether the transaction log is backed up on a daily basis, every so many hours, or every 30 minutes. The transaction log backup occurs at the frequency specified, starting at the time specified in the Full Backup Time Of Day setting. If you selected hourly or every 30 minutes, set the Time of Day to run backup early in the morning.

Delete Old Backups

Determines whether backups are deleted from the Backup folder after the specified amount of time. If you are using Windows Authentication in the data service settings (this can be viewed or changed using the Patriot Configurator), the windows account that the data service runs under will require the sysadmin role in order to allow file deletion.

The simplest way to grant this role to a user is to open SQL Management Studio, and expand the Security > Logins folder.

Security Logins

Go into the properties of an existing login, or add a new one (right click and select "New Login...") and choose the account that the Data Service runs under.
In the Server Roles tab, tick the "sysadmin" role.

"sysadmin" Role

If you are using SQL Authentication with the sa account, it should already have the sysadmin role.

Backup Encryption

note

SQL Server Express and SQL Server Web don't support encryption during backup. However restoring from an encrypted backup to an instance of SQL Server Express or SQL Server Web is supported.

To encrypt during backup, you must specify an encryption algorithm, and an encryption key protector to secure the encryption key. The following options are supported in Patriot for backups:

• Encryption Algorithm: AES 128, AES 192 and AES 256
• Encryption key protector: A database certificate

For instructions on creating and managing certificates and restoring an encrypted database, please refer to the section Backup Encryption Outlines .

Backup Compression

Backup compression is supported on SQL Server editions: Enterprise, Standard, and Developer.

note

Enabling backup compression reduces the size of your backup files, which can save storage space and potentially increase the speed of the backup process due to reduced I/O operations. However, please note that enabling compression can impact system performance because it requires additional CPU resources.

Manual Backups

Manual or one-off backups can be created using the separate Patriot Utilities program, which can be found in the Patriot Version 6 Client installation folder. This is typically C:\Program Files\Patriot Systems\Patriot Version 6 Client. Simply run the Utilities.exe program, and select Maintenance > Database > Database Backups.

Manual backup menu

This program can be run while Patriot is offline, so the SQL login details must be entered before the backup can be run.

Next Enter the backup folder location and select the type of backup.

If you have the Patriot Archive Database enabled then you should normally check Backup Archive Database which will cause a backup of the archive database to be made along with the main database. If you are not using a Patriot archive database then leave this option unchecked.

Click the Save button to perform the backup. A progress bar will indicate the backup progress.

Creating a manual database backup

Backup Encryption Outlines

This section outlines the essential steps for setting up and managing backup encryption using certificates in SQL Server. For the most detailed and up-to-date information, please refer to the Microsoft official documentation.

Sequence for Restoring an Encrypted Database

• BACKUP CERTIFICATE in the old database
• CREATE MASTER KEY in the new location master database
• CREATE CERTIFICATE from the backup certificate of the old database imported to a location on the new server
• Restore a database to a new location (SQL Server)

important

Certificates used for encryption must reside in the master database. If you are using SQL Server replication or have multiple SQL Server instances, ensure that certificates are manually copied to each instance where they are needed for encryption or decryption of backups.

Example of Creating and Managing Certificates

• Creating a Database Master Key (if not already exists)

-- Use master database
USE master;
GO

-- Create master key if it does not exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword123';

• Creating a Certificate for Backup Encryption

-- Create certificate in the master database
CREATE CERTIFICATE BackupEncryptCert
    WITH SUBJECT = 'Backup Encryption Certificate',
    EXPIRY_DATE = '2025-12-31';

• Backing Up the Certificate and Private Key

-- Open the master key for decryption
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourMasterKeyPassword123';

-- Backup the certificate and its private key
BACKUP CERTIFICATE BackupEncryptCert
    TO FILE = 'C:\YourBackupPath\BackupEncryptCert.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\YourBackupPath\BackupEncryptCert.pvk',
        ENCRYPTION BY PASSWORD = 'YourPrivateKey123'
    );

• Restoring the Certificate on Another SQL Server Instance

-- Use master database on the target instance
USE master;
GO

-- Create certificate using the backed-up files
CREATE CERTIFICATE MasterCertificate
FROM FILE = 'C:\YourBackupPath\BackupEncryptCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\YourBackupPath\BackupEncryptCert.pvk',
    DECRYPTION BY PASSWORD = 'YourPrivateKey123'
);
GO