Introduction

Differential backups are a new feature in SQL 7 that can speed up your

recovery times when restoring from database and transaction log backups.

What is a differential backup?

A differential backup is a record of all the pages that have changed since

the last full database dump. They differ from Transaction Log backups in that

they always go back as far as the last full dump, so if you take two

differential backups one after the other, the second contains everything in the

first, plus all subsequent changes.

If you did two successive transaction log backups, the second backup would

only contain changes to the database made after you took the first transaction

log backup.

Creating more efficient backup strategies

The simplest way (and prior to SQL 7 the only way) to ensure you were

able to recover your database to a specific point in time would be to take

regular transaction log dumps, and to restore the database dump and all

subsequent transaction logs up to the desired recovery point.

In this example we take a full backup at midnight, and a transaction log

backup every hour. To recover the database to it's status at 9:30 we have to

first restore the full database dump, then all the transaction log dumps from 1

am to 10 am, specifying "stop at 9:30" for the last (10 am) restore.

This technique works just fine, but we have just had to restore a total of 11

backup files. We can do better than this if we use differential backups. In this

next example we add in a differential backup at 8 am. Now to restore our

database to it's state of play as at 9:30, we only have to restore 4 files, the

full database dump, the differential, and then the transaction log backups from

9 am and 10 am.

We can extend this into the day by making further differential dumps - you

only need to restore the original database dump, the most recent prior differential, and the subsequent

transaction log dumps, to get back to any point in time.

Let's take the example above and extend it later into the day. Here we take a

second differential backup at 5 pm, and we also continue to take out hourly

transaction log dumps. To restore the database to 5:30, all we need to restore

is the original database dump, the second differential dump (remember, it

includes the contents of the previous differential) and the 6 pm transaction log

dump with a "stop at" parameter of 5:30.

In the example above we have only had to restore from 3 files. Without

differential dumps we would have had to restore every transaction log from 1 am

to 6 pm - a total of 19 files when you include the full database dump.

Timing is everything

I chose 8 am and 5 pm for the differential backups in the above example

because it represents more-or-less the boundaries of the normal working day.

Obviously timings for backups should be dictated by your own environment.

Remember - You still need all your transaction log dumps

Differential backups do not give you the ability to stop at a specific time,

so in the above examples if we wanted to restore the database to 4 am, you need

to restore the database dump and the subsequent transaction logs up until the 4

am one. The 5 am differential backup contains the data we need, but

because  it's internal structure is different to that of a transaction log

backup, it does not know how to stop at a given point in time.

About the author

Neil Boyle is an independent SQL Server consultant working out of London,

England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk