For large data volumes, backup job execution can take a lot of time. However, there are several tricks that can help optimize the time.
MySQL Point-in-Time Recovery (PITR)
Point-in-time recovery is the concept of restoring data to a particular time in the past.
Suppose you deleted an important database table at 2 p.m. on a Wednesday. You realize this fifteen minutes later and you need to restore the data. Replication will do you no good, because the table in the replica has also been deleted. Only backups can save the day.
However, if you back up your data at 1 a.m. every day, the closest recovery point to when the table was deleted will be at 1 a.m. that Wednesday. When you restore data, you will lose 13 hours of data. But if you use the point-in-time recovery strategy, you can recover data as of 1:55 pm, losing only 5 minutes!
About MySQL Server Incremental Backups in SqlBak
What are incremental backups?
Incremental backup is a backup that only contain data that has changed since the previous backup, not including all the data in the database.
Why are incremental backups needed?
Incremental backups allow performing backups much more frequently, as they are much smaller in size. However, to restore from an incremental backup, not only the incremental backup file is required, but also the entire preceding chain of backups.
5 ways to reduce SQL Server backup size
SQL Server backup is a page-by-page physical copy of the database files. And, generally, the size of a backup is about the size of the database. However, there are several ways to cut the backup size substantially.
Reducing the backup size has the following benefits:
How to backup and restore SQL Server on Linux
In this article, we will describe how to create a backup of a SQL Server that is installed on Linux.
How does SQL Server create a backup?
You can create a SQL Server backup by running the T-SQL command
BACKUP DATABASE.This command saves the backup file to a specified directory. SQL Server backups are created quickly because it requires just copying data from the database files to the backup file.
When creating a backup, you do not need to stop SQL Server or switch it to single-user mode, and the database continues to work as usual during this operation.
How to connect to MySQL Server using SSH key pairs
In order to backup MySQL Server via SqlBak you have to set a connection via TCP/IP. In other words, SqlBak communicates with a database through port 3306.
Managing the Database Backup of Multiple Servers
While making a database backup is not a difficult task, making one the correct way can be a little more involved. So if you’re new to the process, setting up and maintaining backups on multiple servers can initially seem intimidating.
There is no one-size-fits-all solution. Choosing a good solution depends on your individual architecture, resources and security requirements. In this article, we will discuss the main approaches to managing multiple database backups, and we’ll look at the pros and cons of each approach.
Export/Import SqlBak job in a JSON file
Starting from SqlBak app version 3.2.9, you can export a job to a JSON file and then import it using the SqlBak.Job.Cli.exe utility. This functionality is needed primarily for automated deployment of the SqlBak application and backup jobs creation. A simple tutorial on how to do this is described in the following blog post: How to deploy SqlBak app automatically.
SqlBak.Job.Cli.exe reference manual
sqlbak.job.cli.exe utility is a programming interface for working with the SqlBak application. It is located in the root directory where the application is installed.
This tool can be used to register the application, manage connections to DBMS, and import jobs from JSON configurations.
How to deploy SqlBak app automatically
When SqlBak is installed on a large number of servers, the deployment can be automated in 3 steps:
- Download and install the SqlBak app
- Connect the SqlBak app to sqlbak.com and to a DBMS
- Add a backup job from a JSON file