Backup Types
Published: Dec 16, 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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron