Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Sunday, 18 September 2016

What is Incremental Backup in SQL? Difference between Full, Differential, and Incremental Backup

An incremental backup is a type of backup that only copies files that have changed since the previous backup.
For example, if a full backup was performed on Monday, Tuesday's incremental will back up all changed files since Monday's backup. However, Wednesday's incremental will only back up files that have changed since Tuesday's incremental backup and so on until another full backup is performed.

Sometimes you might hear people talk about full backups, and differential backups versus incremental backups. What is the difference?
A full backup is pretty self-explanatory. It makes a copy of all of your MySQL data.
A differential backup, on the other hand, simply records the differences since your last full backup. The advantage of taking a differential backup is usually the space savings. Most databases have a lot of data that does not change from one backup to the next. Not copying this data into your backups can result in significantly smaller backups. In addition, depending on the backup tool used, a differential backup can be less labor-intensive for the server. If a differential backup does not have to scan all of the data to determine what has changed, the differential backup process can be significantly more efficient.
An incremental backup is a modification of a differential backup. An incremental backup records the changes since the last backup of any type, be it a differential or full backup. The advantages of incremental backups are similar to those of differential backups.
Here is an example that might explain this more clearly. Suppose that you take a full backup on Sunday. On Monday, you make a backup of all the changes since Sunday. This is a differential backup.
On Tuesday is when you begin to see the differences between the backup strategies. If you back up the changes since Sunday, then you have made a differential backup. If you back up the changes since Monday, it is an incremental backup.
Why would you choose an incremental versus a differential backup? That is a little bit out of scope for this blog post, because there are a lot of subtleties to consider. However, perhaps the biggest difference is in the way that you would restore a backup. Suppose that you need to restore your database on Friday. If you have taken differential backups all week long, you only need to restore Sunday, and then apply the changes that have happened since Sunday. If you have taken incremental backups, you must restore Sunday’s backup, and then apply changes repeatedly until you reach Friday. This can be more labor intensive, error-prone, and risky. It can also take longer.
Percona XtraBackup is capable of taking incremental backups, and when you specify the base backup as the previous incremental or full backup, you can easily make differential backups.


Difference between: Full, Differential, and Incremental Backup

Type Definition Benefits Drawbacks
Full Backup: A complete backup of everything you want to backup. Restoration is fast, since you only need one set of backup data. The backing up process is slow.
High storage requirements.
Differential Backup: The backup software looks at which files have changed since you last did a full backup. Then creates copies of all the files that are different from the ones in the full backup.

If you do a differential backup more than once, it will copy all the files, or parts of files that have changed since the last full backup, even if you already have identical copies of those files in a previous differential backup.

For restoring all the data, you will only need the the last full backup, and the last differential backup.
Faster to create than a full backup.

Restoration is faster than using incremental backup.

Not as much storage needed as in a full backup.
Restoration is slower than using a full backup.

Creating a differential backup is slower than creating an incremental backup.
Incremental Backup: The backup software creates copies of all the files, or parts of files that have changed since previous backups of any type (full, differential or incremental).

For example if you did a full backup on Sunday. An incremental backup made on Monday, would only contain files changed since Sunday, and an incremental backup on Tuesday, would only contain files changed since Monday, and so on.
This method is the fastest when creating a backup.

The least storage space is needed.
Restoring from incremental backups is the slowest because it may require several sets of data to fully restore all the data. For example if you had a full backup and six incremental backups. To restore the data would require you to process the full backup and all six incremental backups.