SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...