My last few posts have been backup orientated, I have a post on Full Backup, a post on Transaction Log backups and a note on how to manage backup files. The next in my little series on  backups is a short post on differential backups in SQL Server.

What is a Differential Backup

A differential backup in SQL Server is a backup of all changes made to the database since the last full backup was taken.

How would you use a Differential Backup?

If you have large database that changes by a small amount each day, then to narrow you backup windows and possibly your restore time you make use of differential backups for example if you currently take a full daily database backup of your 250GB database you could possibly reduce the backup window for this database during week by switching the full daily backup to a full weekly backup and taking a differential backup each day in place of the full backup

Or if point in time recovery is important to you, it is for many people, and you take a high frequency of  transaction log backups between each daily full backup, say a transaction log backup every 15 minutes, then you end up with a lot of transaction log backup files that need to be applied in the event of a restore. You can utilise differential backups to reduce the number of transaction logs that need to be applied and at the same time speed up the restore process.

For example, lets say you have a full database backup at 21.00 each day. You take log backups every 15 minutes. If you then find yourself in a situation where you need to restore you database to 20.30 you need to restore from the full backup taken the previous night and then apply all the log files taken since then up to 20.30…That is a lot of files to apply, including the full backup file that’s 95 files in total!

If you add to your back up schedule a differential backup at say every four hours throughout the day in between the full backup, the differential backups will taken at 01:00, 05:00 09:00 13:00 17:00. Then instead of 95 files you will have the full backup, the differential backup, and the 9 transaction log backups taken since the differential taken at 1700. a total of 12 files. which is much more manageable number.

What are the Advantages of using a Differential backup?

The main advantage of a differential backups lie in the fact you have less logs to apply in a  restore scenario as demonstrated above.

You can benefit from a reduced backup window

What are the drawbacks of a differential backup

Any restore is still dependant on the full backup. If the full backup is unavailable, the differentials are not useable.

If you have a highly volatile database that changes regularly, the size of the database backup maybe of similar size and the time to take maybe similar to a full backup, in which you may as well take the full backups.

How do you take a diff backup

This is the code for taking a differential backup of my crickets clubs contact database:

BACKUP DATABASE [CricketContacts] TO DISK = N'C:\Backup\ContactsDiff.dif' WITH
    DIFFERENTIAL, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10,
    CHECKSUM
GO

As you can see it is similar statement for taking a full backup, except we we specify the WITH DIFFERENTIAL statement. I like to give the files the .dif extension too, so I know what type of backup files they are.