Backup types — full, differential, incremental, and synthetic — each handle database transaction logs differently, and understanding that distinction is key to designing and managing effective backup strategies.
Database Backup Types
We are going to be looking at backup types, whether it be full, differential or incremental. We will also look at synthetic backups. But one of the things I want to do is show how databases get backed up, because it is fascinating and drives home the point of these different types of backups. In order to really fully understand that, we have to understand how databases work.
What is a database? A database stores information. Sometimes it is structured, sometimes it is not structured. But let's look at a structured relational database.
Let's first of all imagine we want to store a bunch of people's, well, let's say it is their names and addresses and so on and so forth. So we are going to store a bunch of customer records. A record is a row. Think of an Excel sheet, or a Google Sheets, or some sort of spreadsheet software, where you have these different rows, and each row is going to be a customer. Along there we have columns, and each column is going to be a field: one of them is going to be first name, one will be last name, one of them will be address, city, state, country, so on and so forth.
So this makes up a table. A table has columns and it has rows. Each row is a record, and then each column is a field. Then what happens is those go into a database, and the database has multiple tables, and then it interrelates this information amongst these different tables.
Now, we are concerned more about the backup stuff, so what does this look like? A database is actually just a file. These rows and columns and databases all get put into some sort of format, which is just in a file that is stored in file format.
Here we have our table right here, and what we want to do is add to this table. So we are going to start adding more and more customer records to this table. Here are the rows that we are adding. These additions actually do not go directly into this file. Instead they go into a different file called a transaction log.
So these are the transactions. We are adding users, and so each one of these users is going to be a transaction that gets added to this transaction file. Then at some point in time this transaction file gets what we call committed, and rolls up into the main database file. When you are looking at a database, what it is doing is taking the stuff that is in the actual database file, the database, and then adding all of the transactions to display the actual information.
So what does this have to do with backups? If it is a full backup, then it is just going to take all this information and back it all up. But the other thing that happens with a full backup is that all of these transaction logs get rolled back into the main file, and so now everything is part of the one file. So that is the full right there.
Now, the differential and incremental do not take a copy of the main database file, and there are reasons for that, which we will discuss. But what it is going to do is just take a copy of the transaction logs. If it is a differential, then it is going to just leave those transaction logs and keep adding to them. If it is an incremental, then what it will do is roll these transaction logs back into the main file, and then it will start a new batch of transaction logs.
Once again, there are reasons for all this that we will get into, but just know that how databases get backed up, and how it treats these transaction logs, depends on what type of backup we are taking, what type of backup we are using.
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 →