Full Backup Vs. Differential Backup Vs. Log Backup

When it comes to backing up SQL Server databases, it's critical to know what backup types are there and what you should choose. In order to do this, you must first understand what SQL Server will include in each of these backup files.

If your database is in the "Full Recovery" model, you have the option to choose whether you want to back up
the data file, log file or both.

You will be given these options:

To illustrate we will consider a hypothetical database which we will be backing up hourly.

Full Backup

As the name implies full backup will include all the details (data), every time you back up the database.

Consider the above case, where we will take full backup hourly.

Full Backup #01 will contain all the data which was there since the beginning.

Full Backup #02 will contain all the data including the changes that happened between 8:00am - 9:00am

Full Backup #03 will contain all the data including the changes that happened between 9:00am - 10:00am

The backup file size will grow since it will contain everything all the time. You need only one backup in order to restore the database and it will take the database to the status (or time) when the backup was initiated.

Differential Backup

Unlike the full backup, the differential backup will only contain the changes that happened since the last full backup

Full Backup #01 will contain all the data which was there since the beginning.

Differential Backup #01 will contain only the data changes that happened between 8:00am - 9:00am

Differential Backup #02 will contain only the data changes that happened between 8:00am - 10:00am

The differential backup size is comparatively less than the full backup since it only contains changes since the last full backup. You need to have both full backup and the differential backup (last one) in order to restore the database.

Log Backup

The log backup will only backup the transaction log file. It will not contain any data pages from the data file. A log backup will always contain changes in the log file since the last log backup.

Consider the above case. Even though the database is fully backed up at 8:00AM the first transaction log backup taken at 9:00am will contain details since 7:00am.

Full Backup #01 will contain details from both data and log file as of 8:00am

Log Backup #01 will only contain details from the transaction log between 7:00am - 9:00am 

Log Backup #02 will only contain details from the transaction log between 9:00am - 10:00am 

With the log backups, you can take the database to any time of the day (in this case up to hourly blocks). But you need to retain all the log backups in order to do that.