There are many different approaches that can be implemented for database backups. In part, this is dependent on the organization, the size of the database, and how often the database is used. The most common options within most databases are a full database backup, a differential backup, and an incremental backup.
The simplest type of backup is a full database backup. This provides a copy of the entire database in which we can restore the data to a point in time in which the database backup was made. The useful piece about database backups is that you can have database transactions running even when the database is still in the process of backing up the data. However, database backup input and output operations can slow down some of those transactions. This approach of just running a full backup is an option that a lot of organizations will use on a nightly basis. It is a good plan if the database size is relatively small. In this type of scenario, each backup file is small and can easily be restored independently if there are any issues. However, if the database is large in size, it can take a lot of time and space to constantly create full backups on a nightly basis. This type of backup would be the fastest to restore as it is only one step.
Differential backups are not meant to run on their own but rather differential backups only contain the data that has changed since the last full backup. The differential backups are cumulative and not incremental. This means that the differential backup will have all of the changes since the last full backup even if there are other differential backups run since the last full backup.
If we ran the full backup on Sunday and ran the differential backup every other day. The differential backup on Monday would only contain Monday’s data. The differential backup on Wednesday would contain Monday, Tuesday, and Wednesday’s data even though the differential backup ran on Monday and Tuesday. Saturday’s differential backup would contain all of the data from Monday to Saturday. As such if we used a full backup and differential backup strategy, if we would need to restore the data, we would need at most 2 restores. One of the full backup and then one of the differential backup. This approach is frequently used when you have to create database backups more frequently. Since the differential backups only contain the data changed since the last full backup, they would be created a lot faster than a full backup.
The incremental backup is similar to a differential backup but the difference is that each incremental backup only contains the data from the last backup regardless if it is a full backup, differential backup, or incremental backup. If we ran the full backup on Sunday and ran incremental backups every other day, the incremental backup on Monday would have Monday’s data. The incremental backup on Tuesday would only have Tuesday’s data. Saturday’s incremental backup would only have Saturday’s data. These incremental backups would run in the least amount of time and have the least amount of space. However, they can take the longest to restore as each incremental backup would have to be applied one after another. If the data from one incremental backup was missing, the incremental backup files after that would not be useful as we would be missing the changes.
Source: Authored by Vincent Tran