Calculating DB backup size

Question

Is there a way to know the size of a database full backup file before we actually do the backup? I did some analyses and figured sp_spaceused could give a close figure but thats not all, there seem(s) to be one/more factors other than the used space that go into determining the size?

Has anyone been down this path before? Any ideas?

I am looking for a more specific answer. I checked the AdventureWorksDB bak size and the MDF and LDF sizes. That did not add up correctly. i.e. the bak = 164,755 KB ; MDF = 180,992 KB and LDF = 2048 KB. — Gnana
– Gnana, Commented May 14, 2009 at 11:16
As an aside, according to serverfault.com/a/484672/82769, an MS SQL compressed backup will typically be ~30% smaller than uncompressed. — user565869
– user565869, Commented Aug 1, 2014 at 22:24

Jonathan Kehayias · Accepted Answer · 2009-05-28 02:08:36Z

8

sp_spaceused should be pretty close if you look at the reserved space.

Estimating the Size of your Database Backups

Paul Randal blogged recently about how to know how large your TLOG backup might be:

How much data will the next log backup include

A combination of the two values, seems to be really close to the actual backup size in some brief testing.

answered May 28, 2009 at 2:08

Jonathan Kehayias

3,4121 gold badge25 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gnana Over a year ago

thank you, I have reached thus far. Is it possible to get any further?

Jonathan Kehayias Over a year ago

In retesting this, the numbers are only a few KB off in size from the actual backup that is taken I don't know how much closer to accurate you are expecting to get?

cjk · Accepted Answer · 2009-05-14 07:38:48Z

0

It's pretty much the size of the mdf and ldf files.

answered May 14, 2009 at 7:38

cjk

46.6k9 gold badges83 silver badges113 bronze badges

1 Comment

Jonathan Kehayias Over a year ago

This isn't correct since data files have free space that is not backed up. You can test this by creating a 4GB database and then doing a backup. The backup will be nowhere near 4GB in size.

Collectives™ on Stack Overflow

Calculating DB backup size

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related