DBA Myths: Can a differential backup get bigger than the full backup?

7

February 14, 2019 by Kenneth Fisher

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.

7 thoughts on “DBA Myths: Can a differential backup get bigger than the full backup?

  1. Phil says:

    Database data growth can make the diff bigger also.

  2. […] Kenneth Fisher notes that differential backups can end up being larger than full backups of the same…: […]

  3. I generally recommend a full backup if the differential approaches 80% of the full size.

  4. John Mitchell says:

    Good post, Kenneth. Would it be fair to say, however, that a differential backup will never be larger than a full backup would have been if it had been made instead?

    John

  5. […] differential file could get as large as the full backup but no larger (I was WRONG), although it probably […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,492 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: