Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.