This guide contains recommendations for setting up a SqlBak backup job. This information has been developed and collected over years of interaction with SqlBak users. There will be no theory and formulas, only practical advice.

Note that these practices are not the only solutions. They are suitable in most cases, but can be fundamentally wrong under various circumstances.

Let’s consider the steps that are used to set up a backup job.

Selection of databases to be backed up

SqlBak does not backup the entire database management system, but each database individually.

For example, databases with payment information should be backed up as often as possible. It is reasonable to give such databases their own dedicated backup job that launches frequently, for example, every 15 minutes. In such a job, it is recommended to use differential and transaction log backups, or incremental if MySQL Server is used.

Less important databases should be placed in a separate job. Just use a “backup all non-system databases” option and add to the exclusion list databases from the first backup job. In this way, when a new database will be added, there is no risk that it will not be added to the backup job, since it will be picked up automatically.

IMPORTANT: Choosing a location for storing backups

The Cybersecurity and Infrastructure Security Agency recommends using the 3-2-1 rule for choosing a place where the backups will be stored:

3. Keep 3 copies of any important file: 1 primary and 2 backups.
2. Keep the files on 2 different media types to protect against different types of hazards.
1. Store 1 copy offsite (e.g., outside your home or business facility).

In SqlBak, several different backup storage locations can be set as well as time on how long the backups will be stored in each destination. It’s very dangerous to use only one storage location to store the backups.

1. Local folder on the same server where the databases are located. The storage period in the folder can be set to a minimum (for example, one day). This backup is needed to quickly restore data from it in case of a database breakdown on the next date.
2. Any cloud provider that uses OAuth authorization (DropBox, Google Drive, One Drive). As a rule, the cost of data storage is high here. Therefore, the duration of backup storage can be limited, for example, to 30 days. The advantage of the listed backup storage locations is that they all have a good and independent authorization system.
3. AWS S3, Azure Blob storage, Wasabi. The advantages of these storage locations are that they have specialized settings for storing backups, such as:
   • Choice of storage class with low cost for long-term storage – the backups can be stored for many years
   • Settings for preventing changes and deletion of files – excellent protection against Ransomware attacks

Here you can find more details about the destinations.

Emergency Destination

A SqlBak destination can be marked as an “Emergency destination.” If this option is enabled, then SqlBak will send a backup there only if there were errors during uploading the backup to the main storage location.

In 99% of cases, backups will not be sent to the emergency destination. As an emergency destination, a legacy server (with SSH access) can be used, or, for example, Google Drive. This is a useful reassurance that only takes time to set up.

Schedule Backups

The classic practice is to perform backups during the minimum load on the server, usually at night. This is generally correct, however, a backup once a day is quite rare for important data. MySQL and SQL Server allow performing incremental backups that do not contain all the data, but only data that has been changed. To restore these backups not only incremental backups are required, but also the backups preceding them.

Incremental backups can be performed non-stop, even every three minutes, but this will lead to a very long backup chain, which has its own risks. For example, an error in one backup that is part of the backup chain will not allow you to restore the entire backup chain.

A reasonable balance should be struck. Consider examples of schedules.

 

Send Email Confirmation

SqlBak has been designed in such a way that the user only needs to set up a backup job to enjoy automatic backups. You can control the backup through email notifications. We recommend that you make sure to specify at least one address in the “On failure email to” box, during your backup job settings, in order to be aware if something does not go according to plan.

There is always a risk that something could go wrong. The storage space will run out, the credentials for a storage or databases connection will be changed, or a sector reading error will occur in a database (a broken database cannot be backed up, it needs to be restored). There is a huge number of reasons why a backup may fail, and the vast majority of them are easy to fix.

Please note that a SqlBak email notification contains a job progress log, which many email clients may perceive as spam. To avoid this, create a rule in your mail client for noreply@sqlbak.com and move it to a directory.

The email confirmation option can be tested by clicking on the icon.

Custom SMTP Server

SqlBak sends email notifications via AWS SES. It has one annoying feature – if an email notification could not be delivered within a certain time interval, then AWS SES adds such an email address to its blacklist. Notification that an email address has been added to the AWS SES blacklist will be sent to a customer, and by the customer’s request, we remove the email from the blacklist manually.

Such problems rarely occur and are usually associated with custom SMTP Servers that for some reason could not accept the email (for example, they were turned off). If a custom SMPT Server is used as the main email server, then we recommend for safety that you specify at least one address from Gmail, Yahoo, Outlook, Yandex, or another similar provider.

Backup Compression

By default, a ZIP as an archive format is used.

However, if you are not limited by any requirements that require you to use ZIP, then we recommend using 7zip. To do this, you need to install the 7-zip utility. 7-zip has two main benefits:

1. 7-zip compresses faster and better
2. For 7-zip, additional compression options can be set. For example, the number of threads for compression can be set. That will allow limiting the load on the processor or vice versa to speed up compression. Fine-tuning compression is especially useful for large databases.

Files and Folders Backup

SqlBak has an additional option that allows you to perform backups of your files and folders. SqlBak does not make a transactionally consistent snapshot, instead, it creates a regular archive. Note, only full backups can be performed for files and folders.

In the future, we plan to significantly improve the files and folders backup option.

Encrypt compressed files

We recommend that you always use the encryption option because if a hacker gains access to your storage where backups are stored, your data will still be protected.

The passwords that are specified in the settings of your backup jobs are stored encrypted on the SqlBak server. When restoring a backup, SqlBak will enter the password itself and no action is required from you.

Note, unlike the password required for authorization on the site, the archive password is very vulnerable to brute-force-attack. To minimize risks, use the longest possible password, preferably in the form of a sentence. A shorter password such as “x8)oL%4&” can be figured out in a matter of hours. But the heat death of the universe may occur before a longer password such as “MyImaginationRulesTheWorld” can be cracked.

Job Options

By default, the values in the “Job Options” section are at the recommended settings. Edit them only if you understand why they are needed and what they do.

In the “Job Options” section, we recommend that you pay attention to the following:

SQL Server

1. SQL Server Backup Compression – This is SQL Server compression that runs after a backup is created. This option can significantly speed up a backup job, but it will increase the load on a database.
2. Backup broken chain behavior – To restore from Differential or Transaction Log backups, the previous backups (full, differential, and transaction log) are required. If one of the previous backups was created by another software, a backup chain will be broken and SqlBak will not be able to restore that database. You can set the “Generate Error” value so that a job will generate an error in this case and you will be notified.

MySQL and PostgreSQL

The default settings are recommended. Most of the additional settings for MySQL and PostgreSQL backups are focused on how the output sql dump will be generated. Changing some of these options will result in SqlBak not being able to automatically restore the data. Change these values only if you clearly understand what it will affect.

Restore tasks

As a rule, automatic restore after backup is used to create a test database for development. However, even if a test dev server is not needed at all, we still recommend setting up an automated restore to ensure that the backups can be restored when it will be needed.

General Recommendations

Backups should be protected from:

1. Server or database failure. A variety of problems can happen, for example, a server could be broken due to a hardware issue.
2. A software issue that interacts with a database. There may be errors in the algorithm interacting with the database, leading to data corruption. For example, when updating one of the fields in a row, the other field is set to NULL by mistake.
3. Human factor. A wrong table or database can be deleted accidentally.
4. Hacker attack. The easiest way for a hacker to blackmail a user is to encrypt the user’s sensitive data. But if the user has a backup in a place inaccessible to the hacker, then blackmail is doomed to failure.

What are the data protection requirements?

There are two main parameters that should be decided:

1. Recovery Point Objective (RPO) – How much data you can afford to lose in the case of a failure determines how often you need to back up.
2. Recovery Time Objective (RTO) – How much time can you spend on restoring your database determines where and how long the backups should be stored.

Test your backups

The most important thing is not how you set up backups, but whether you forget to test them. The best way to test a backup is to restore it. Of course, there is no need to break your production server in order to test a backup. Instead, try to recreate your production server on a different machine. Then, you will practice restoring, understand how long it takes, and also make sure that you really backup everything you need and do not forget anything.

Contact us

If you have any recommendations on how to improve this guide or you want a particular functionality to appear in our service, write in the comments to this article or to our support team.