EmergencyEMERGENCY? Get 24/7 Help Now!

What Are Full, Incremental, and Differential Backups?

 | January 23, 2012 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
Baron Schwartz

Baron is the lead author of High Performance MySQL. He is a former Percona employee.

9 Comments

  • Interesting distinction with ‘differential’ vs ‘incremental’, Baron. I had honestly not considered using the incremental backup feature of XtraBackup to ever backup the changes of anything other than your most recent backup, whether it have been a full backup or incremental backup.

    In XtraBackup Manager I tried to offset the time spent doing say, a restore on Friday, applying each set of incremental changes one after another since the full backup on Sunday, by providing a “materialized backups” feature, whereby XtraBackup Manager always maintains a fully materialized “restore” of the latest backup you took, so all the restore would require is copying that data to the restore target, not applying all the deltas. This is obviously at the expense of storage space.

    I might have to consider adding another “Backup Strategy” to XtraBackup Manager that uses the differential method, rather than incrementals…

  • This article describes full, incremental, and differential backups. The Backup tool in Windows 95 supports full and incremental backups. Differential backups are not supported.
    Backup uses backup file sets to help you manage backing up drives, folders, or files. Incremental and differential backups rely on an initial full backup of the drives, folders, or files in question.

  • Can you please give examples of data usage with each of the types of backup e.g. A backup done once per week using full on the first week, then incremental for 7 weeks thereafter. Using 25MB on week1 then various differences for each of the weeks. Can you give an example in a table? I am doing a project with Open university, and would appreciate your help!

  • Im having the same issue with that project. i could really use some help right about now. its been wrecking my head for a while nw

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.