The thing about DBA Myths is that they are generally widespread and widely believed. At least I believed this one until I posted What’s a differential Backup? and Randolph West (b/t) pointed out that my belief that differential backups can’t get larger than full backups was incorrect. In fact, differential backups (like FULL backups) contain enough transaction log information to cover transactions that occur while the backup is taking place. So if the amount of data that needs to be backed up combined with transactions requires more space than just the data ….

Demo time!

-- Setup
use master;
GO
CREATE DATABASE BackupTest;
GO
USE BackupTest;
CREATE TABLE TakeUpSpace (
	Id INT NOT NULL IDENTITY (1,1),
	Col1 char(500),
	Col2 char(500),
	Col3 char(500),
	Col4 char(500)
);
GO
-- Load data
INSERT INTO TakeUpSpace 
SELECT
	REPLICATE('a',500), REPLICATE('b',500),
	REPLICATE('c',500), REPLICATE('d',500)
FROM master.dbo.spt_values;

INSERT INTO TakeUpSpace 
SELECT
	REPLICATE('e',500), REPLICATE('f',500),
	REPLICATE('g',500), REPLICATE('h',500)
FROM master.dbo.spt_values;

INSERT INTO TakeUpSpace 
SELECT
	REPLICATE('i',500), REPLICATE('j',500),
	REPLICATE('k',500), REPLICATE('l',500)
FROM master.dbo.spt_values;
GO 100

-- Take an initial backup
BACKUP DATABASE BackupTest TO DISK = 'c:\temp\BackupTest.bak';

-- Update every row
UPDATE TakeUpSpace SET Col4 = 'Z';

At this point, I’ve created a database that has ~1.5gb of data. The full backup was ~16mb compressed. Next, I updated every row. This was just an easy way to make sure that I hit every extent in the database so all of them will be backed up with the differential. This means that the differential should be the same size (or at least close to) as the FULL.

BACKUP DATABASE BackupTest TO DISK = 'c:\temp\BackupTest1.DIF' WITH DIFFERENTIAL;

The compressed size of the differential is fractionally smaller than the full. (16,232kb vs 15,813kb) Pulling the information from msdb gets me the actual size (non-compressed) and again, just slightly smaller (1489mb vs 1487mb). So next comes the attempt to make a differential that’s actually larger than the full.

In a seperate session run the following:

UPDATE TakeUpSpace SET Col4 = 'W';
UPDATE TakeUpSpace SET Col4 = 'X';
UPDATE TakeUpSpace SET Col4 = 'Y';
GO

While this is running (it took a minute or two on my laptop) run a differential. Note: Make sure the differential is running while the transaction is running.

BACKUP DATABASE BackupTest TO DISK = 'c:\temp\BackupTest2.DIF' WITH DIFFERENTIAL;
GO

And lastly to make sure that the full backup size didn’t change (no reason why it would have, but you know, proof) run another full backup.

BACKUP DATABASE BackupTest TO DISK = 'c:\temp\BackupTest2.bak';
GO

And here we go

Actual Files

msdb

So there you go. Differential backups can, in fact, get bigger than FULL backups. Which of course brings up a good point. Once your differential gets to a certain percent size of your full it’s probably time to take another FULL. I’m honestly not sure what the best practice is but I’m going to guess if you’ve hit 25%-50% of the FULL size then it’s time to take a new FULL.