Backup Types
Published: 3rd May 2013
There are 3 backup types within SQL Server… Full, Differential, and Transactional.

The reason for there being 3 types are not immediately obvious but understanding them is key to having a good backup strategy within your system. These are also relevant to the Recovery Mode of your databases.

A Full backup is simply a backup, nothing more. As the name implies, it takes a full point in time backup of your whole database.

A Differential backup does give you a clue in the name… it takes the difference between the time you take the backup and the last full backup that was taken. No matter when you run this backup or how many times, it is cumulative and will always take a backup of all changes made since the last full backup, NOT since the last differential.

A Transactional backup covers the transactions since the last backup was taken (whether it is full, differential, or transactional).

Therefore if you had a database in FULL recovery mode with the following plan:

Full backups nightly
Differentials every 6 hours
Transaction log backups every hour

It is now 9pm and you want to restore the database to its 8pm state, therefore you would restore the following:

Full backup
6pm Differential backup
7pm Transactional backup
8pm Transactional backup

As mentioned above, the Differential backup is cumulative and holds every change since the full backup, therefore you only need the one, whereas the transactional backups are not cumulative and therefore you need each relevant backup restored in order.

NB: Comments will only appear once they have been moderated.