Diffrence between Full Backup vs Differential Backup vs Transaction Log Backup

Full vs Differential vs Transaction Log Backup:

SQL Server provides three backup options - Full backup, Differential backup and Transaction Logs backup. Today, we will review the three backup strategies and key differences between them.

Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.

Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.

Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.

You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.

Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.

The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.

If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.

It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.

Differential backup:

differential backup offers a middle ground by backing up all the files that have changed since the last full backup. That is where it gets its name: it backs up everything that's different since the last full backup.

In the image below you can see an example on how a differential backup would look like for a backup job that runs four times:

• • 

Restoring a differential backup is a faster process than restoring an incremental backup because only two backup container files are needed: the latest full backup and the latest differential.

Backup4all uses the information it has recorded in its catalog file (.bkc) to determine whether each file has changed since the last full backup.

Use differential backup if you have a reasonable amount of time to perform backups. The upside is that only two backup container files are needed to perform a complete restore. The downside is if you run multiple differential backups after your full backup, you're probably including some files in each differential backup that were already included in earlier differential backups, but haven't been recently modified.

Advantages:

1. Restore is faster than restoring from incremental backup
2. Backing up is faster than a full backup
3. The storage space requirements are lower than for full backup

Disadvantages:

1. Restore is slower than restoring from full backup
2. Backing up is slower than incremental backup
3. The storage space requirements are higher than for incremental backup