Does SQL Server have incremental data backups?

Question: I’m responsible for some large SQL Server databases and I’ve been trying to implement incremental data backups on them. I can do this in our DB2 systems but I’m seeing unexpected behavior in SQL Server around the backup sizes. Can you explain please?

Answer: A true incremental data backup only backs up the portions of the data files that have changed since the last incremental data backup. SQL Server cannot do incremental data backups, but many people do use the word ‘incremental’ to describe SQL Server backups – which causes confusion. (See also, "Combining CDC and SSIS for Increment Data Loads" and "Step-by-Step Approach to Differential Backup and Recovery").

SQL Server has two kinds of data backups – full and differential backups, which can be performed at the file, filegroup, or database level. A full database backup backs up the entire database (to be specific, all the allocated portions of the data files). A differential database backup backs up all portions of the data files that have changed since the most recent full database backup.

This means that successive differential database backups after a full database backup will get larger and larger as time progresses and more data in the database changes. This is what confuses people when they refer to differential backups as incremental backups – “how can the incremental backups be getting bigger and bigger ?!?”

Differential backups in SQL Server are best described as cumulative. At some point it may make more sense to chose to take a new full backup instead of another differential backup. To do this you can use a script I blogged a few years ago that will tell you how large the next differential backup of a database will be – see this blog post.

The closest kind of backup that SQL Server has to an incremental backup is a transaction log backup. A transaction log backup will back up all the transaction log generated since the most recent transaction log backup.

However, transaction log backups are very different from data backups when it comes to the actions that occur when a restore operation is performed. For a data backup, the restore simply lays down the data from the backup into the data files. For a transaction log backup, the restore has to reply the database changes described by the log records in the backup – a much slower set of operations.

To summarize: it is safer to not use the word “incremental” at all when describing SQL Server backups and instead use “full”, “differential”, and “transaction log” to describe the different kinds of SQL Server backups.

As far as backup strategy is concerned, that’s beyond the scope of this column but a good primer can be found in a TechNet Magazine article I wrote in mid-2009 – see here.