Predicting backup size of the database

Question

Post reply

Predicting backup size of the database

Divine Flame

SSCoach

Points: 15943
More actions
November 2, 2010 at 3:50 am

#230616

[font="Times New Roman"]
Hi All,
Is there any way to predict the full backup size of a database before actuly taking its backup ?
Any help will be appreciated 🙂
Regards,
Sujeet
[/font]
Sujeet Singh

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

It should be close to used space in the data file plus little more (some portion from active log file).

Pradeep Singh

Grant Fritchey SSC Guru Points: 398720 More actions · Answer 2

Yep, what he said. Generally the size of the data stored will define, in pretty close approximation, the size of the database. You can get the size a number of ways. sp_spaceused is one way. There are tons of others, SMO, PowerShell, for example, check this link.

"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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Grant Fritchey (11/2/2010)
Yep, what he said. Generally the size of the data stored will define, in pretty close approximation, the size of the database. You can get the size a number of ways. sp_spaceused is one way. There are tons of others, SMO, PowerShell, for example, check this link.

It's actually dead on for me, within 1.4% of the actual backup size using native sql backup without compression.

Grant Fritchey SSC Guru Points: 398720 More actions · Answer 4

Ninja's_RGR'us (11/2/2010)
Grant Fritchey (11/2/2010)
Yep, what he said. Generally the size of the data stored will define, in pretty close approximation, the size of the database. You can get the size a number of ways. sp_spaceused is one way. There are tons of others, SMO, PowerShell, for example, check this link.
It's actually dead on for me, within 1.4% of the actual backup size using native sql backup without compression.

That sounds like what I would expect. Any significant variation means something is up. It's just not going to be 1:1.

"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

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ sujeet

Run this query against your database and see the results. The UsedSpaceMB will give you an idea as how much will be the .bak file too (they should be almost similiar):--

select [FileSizeMB] = convert(numeric(10,2)

, round(a.size/128.,2))

, [UsedSpaceMB] = convert(numeric(10,2)

, round(fileproperty( a.name,'SpaceUsed')/128.,2))

, [UnusedSpaceMB] = convert(numeric(10,2)

, round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))

, [DBFileName] = a.name

from sysfiles a

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Divine Flame SSCoach Points: 15943 More actions · Answer 6

[font="Times New Roman"] Hi All,

Thanks a lot for your replies. They are really helpful. Appreciate it 🙂

Regards,

Sujeet

[/font]

Sujeet Singh