Backup size

Question

Backup size

Krasavita

SSC-Insane

Points: 23138
More actions
August 20, 2012 at 12:59 pm

#260314

Hello,
Before I run backup,can I check how big it will be?
Thank you

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Not really. You can get a general idea, in that it's usually smaller than the database (assuming free space in the data and log files).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

This will tell you the minimum size (the size of your MDF and NDF files). The backup, unless compressed, will never be less than this.

USE {myDB};

SELECT SUM(size/128) AS mb FROM sys.database_files WHERE type = 0

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Actually it probably will be.

That query returns the total size of the DB files. A backup does not back up the entire file, just the used portion of it. So if you have a 10GB database that's only 50% allocated, the backup will (ignoring contribution from the log) be around 5GB in size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

GilaMonster (8/20/2012)
Actually it probably will be.
That query returns the total size of the DB files. A backup does not back up the entire file, just the used portion of it. So....

Good catch. Did not think about unused space. :blush:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SQL Show SSCertifiable Points: 6143 More actions · Answer 5

Use dbname

Go

Select count(Total_pages), count(used_pages) , sum( total_pages* 8) TotalPagesKb, sum(used_pages * 8 ) Usedpageskb from sys.allocation_units

I am not sure above query will help you 100%, but you may get an idea about of your backup size will be.