Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Speedy Database Recovery with Differential Backups

By Neil Boyle,

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

Total article views: 7405 | Views in the last 30 days: 2
 
Related Articles
FORUM

Restore multiple Differential backups

Restore multiple Differential backups

BLOG

SQL Server RESTORE DIFFERENTIAL BACKUP WITH SIMPLE RECOVERY

First thing this morning – a request from a DBA to confirm if Differential BACKUPS can be restored o...

FORUM

Differential Restore

SQL Differential Restore Script

SCRIPT

Restore database with all necessary files

It creates the script for restoring your database with all the backups (full, differential and trans...

FORUM

differential restore

how can i restore a DataBase in differential mode ?

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones