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

, 2019-03-04 (first published: )

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads