Database backup strategies rely on transaction logs to track and manage changes across full, incremental, and differential backup types. Understanding how these logs are committed or retained determines how each backup method captures and restores database records.
Database Backup Types
There are some cool effects when it comes to backing up databases. It uses something called transaction logs, and it uses these transaction logs to back up a full, differential or incremental backup. Let's take a look at the interplay with that and see how it works.
A full backup would back up the whole folder. Let's say we did a full backup at the beginning of the day — it would back up the whole folder.
The differential: let's say if we did a differential at 1M, it would back up all of the changes that have happened since that last full backup, even if you were doing differentials on an hourly basis.
An incremental will just back up whatever the changes were since the last backup. So if you were doing incrementals throughout the whole day, then at 100 p.m. that incremental would just back up four and five, just the changes that have happened since the last backup, which would have been 12:00 p.m.
We're going to see how that applies to a database. Remember that a database has records, and those records go into tables, and those tables make up a database. Essentially a database is a file; if you look on the back end of it, there's just a bunch of files that are holding the information from that database.
A database table has a bunch of different records involved in it. This is a table within that database, and this particular one has an ID, company, first name and last name. These records are actually stored in some sort of file format somewhere on a drive.
We have something called a transaction log. What a transaction log does is it tracks the changes. Let's say we made three more entries into this table — that would be recorded onto this transaction log. At some point this transaction log would then get rolled up into the main file, the main database, and we call that a commit.
A full backup will back up the database and all the transaction logs, and then once it's backed up it'll actually roll these transaction logs into the database.
An incremental backup essentially does the same thing with one change: it doesn't back up the main database, it only backs up the transaction logs. Then after an incremental backup takes place it rolls those into the main database. Then it will start creating new transaction logs for all of the new transactions, and once those are created the next incremental backup will take those, save them, and then roll those changes in there. So you can see that an incremental backup is just backing up all of the data since the last backup.
Differential backups won't commit the changes after they are backed up. That is, they will take a copy of all of the changes but then not roll them back into the database. What happens is new transaction logs are created after that, and the next differential backup takes all of those. So a differential backup takes all of the records since the last full backup.
What we end up with is the same thing as if we were to have a full, differential and incremental on our files and folders that we've mentioned before, except that it just uses transaction logs to figure out whether it needs to back up the data or not back up the data.
TechKnowSurge builds IT and cybersecurity professionals through hands-on, concept-first training built around real understanding — not memorization. Free interactive tools, structured programs, and 25+ years of real-world experience, all in one place.
Explore free tools and programs →