Differential Backup size

Question

Differential Backup size

cheshirefox

Hall of Fame

Points: 3464
More actions
December 15, 2015 at 2:42 pm

#399477

Hi -
Is there a simple way to estimate the size of a differential backup (uncompressed)? I've checked out the script from P. Randall in 2008.
Anything more recent?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it would really depend on the database. you'd want to keep track of the sizes some diff packups of the db over time for a while.

if the database just receives occasional updates, vs lots of updates, vs a truncate and reload scenario, all will have different growth and size patterns.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cheshirefox Hall of Fame Points: 3464 More actions · Answer 2

It's a very volatile OLTP environment, with many, many IUD's happening throughout the day.

I'm trying to estimate (by executing a script, if one exists) that size of individual diff backups post full backup.

Thanks in advance.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 3

A simple way? None that I'm aware of.

I did a search. The script documented here comes up pretty frequently, but it's anything but simple.

Looking at Paul's script, I'm pretty sure it'll still work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 4

take a look at this article: https://dougzuck.com/sql-differential-backup-size-prediction

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 5

What's your exact use case for this? I can kinda see where it would be useful in some limited circumstances, but otherwise there's not much point.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

akrap SSC Journeyman Points: 81 More actions · Answer 6

I could think of one way, but I wouldn't recommend it. Create snapshot of your database immediately after your full backup. Look at the file size using compact.exe. For example:

compact.exe AdventureWorks_Snapshot.ss