SQL Server - Rain or Shine

 SQL Server backup types

Full, Differential backup and Transaction log backup.

FULL Backup:

A full backup contains all the data in a database from the creation of the database till the point of Full Backup initiation. It has enough log information and all data from file groups or files associated with Database to allow for recovering that data. It is used as base for differential and transaction log backup.

Differential Backup:

A differential backup is not an independent backup it always refers latest full backup as its starting point. It backup’s all the data from Full backup to the point of initiation of differential backup. Differential backups are faster and smaller to create than a full database every time.

 For example; Lets consider we take a full backup F1 at 12.00 am midnight every day and differential backup every 2 hours during the day.

Differential backup D1 starts at 2.00 am is about 1 GB. Differential Backup D2 at 4.00 am is about 1.5 GB. Differential backup D2 contains all the data from Full backup i.e from 12.00 am to 4.00 am including the data from differential backup D1.

 D2 = D1+ Data from 2 am to 4 am.

 D2= 1GB + 0.5 GB= 1.5 GB.

Should a point of failure happens at 4.05 am  by using backups  F1+D2  we can restore until 4.00 am. At restore time, the full backup is restored first, followed by the most recent differential backup.

Transactional Log Backup (FULL and Bulk logged recovery Type)

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.

The transaction log backups are only valuable under the full recovery model or bulk-logged recovery model. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model, an unbroken log chain lets you to restore the database to any point in time.

Just like differential backup, transaction log backup is also based on full backup. Therefore, before you can create the first log backup, you must create a full backup, such as a database backup. Because it requires less disk space than full backup, you can create them more frequently than database backups.

For Example: You take 1 full backup everyday at 12 am and differential for every 2 hours starting from 2.00 am and log backup’s every 5 minutes.  If a point of failure happens at 4.04 am. You need F1+D2+ Transaction log after D2 to restore till 4.04 am.