For large data volumes, backup job execution can take a lot of time. However, there are several tricks that can help optimize the time.
Parallel Execution of Backup Job
SqlBak works in the following way:
- Creates a backup file into a temporary folder
- Compresses the backup file into the temporary folder
- Removes the backup file after the compression
- Sends the compressed backup file to the selected destination places
- Removes the compressed backup file from the temporary folder
However, it is possible to create several backup jobs (one job for each database or group of databases) and schedule their launch at the same time.
Parallel execution uses more server resources, but is faster.
Compression
Compressing large files can take a lot of time. There are two main parameters that allow you to regulate the time spent on compression. This is the compression thread priority and the degree of compression. Increasing the compression thread priority and reducing the degree of compression will reduce the compression time.
7zip Parameters
If your server has many unused processor cores, you can significantly increase the compression speed by using the LZMA2 compression algorithm. This algorithm supports up to 16 parallel threads and can dramatically increase compression speed.
Use the following parameters in the “7-zip command line switches” field to activate the use of the LZMA2 algorithm with 16 threads.
-m0=lzma2 -mmt=16
Incremental backups
Backing up a large database will take a lot of time, no matter what. The more data, the more time it takes. However, it is not necessary to backup all data. You can backup only those data that have been changed. These backups are called incremental backups.
Incremental backups cannot be a complete replacement for a full backup of the database. From time to time, a full backup is still necessary so that the incremental backup chain does not become too long.
Differential and Transaction Log Backup for SQL Server
For a SQL Server database, three types of backups can be performed:
- Full – Backup all data
- Differential – Backup only changes that have been made since the last full backup
- Transaction Log – Backup only changes that have been made since any previous backup
A backup plan can be set at the “Advanced backup schedule” settings:
In the following blog post, you can find more details about SQL Server differential and transaction log backups.
Binlog backup for MySQL
MySQL records all transactions in special files called binary logs. SQL statements can be extracted from these files and executed again, thus restoring the data.
Such backups are performed quickly, as they are simply file copying.
More about MySQL Incremental backups
Special destination settings
For Azure Blob Storage and AWS S3, the number of parallel file upload threads can be specified. With a high ping but a wide internet channel, this can significantly speed up the upload.
