Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Backup size
Add to briefcase

Backup size Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Krasavita
Posted Monday, August 20, 2012 12:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840
Hello,

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

Thank you
Post #1347413
GSquared
Posted Monday, August 20, 2012 1:11 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 15,442, Visits: 9,572
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
Post #1347423
Alan.B
Posted Monday, August 20, 2012 4:03 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 237, Visits: 1,195
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

-- AJB
xmlsqlninja.com
Post #1347500
GilaMonster
Posted Monday, August 20, 2012 4:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334
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 2008, MVP
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

  Post Attachments 
2012-08-21 00-21-40.jpg (51 views, 85.99 KB)
Post #1347502
Alan.B
Posted Monday, August 20, 2012 6:00 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 237, Visits: 1,195
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.

-- AJB
xmlsqlninja.com
Post #1347518
SQL Show
Posted Monday, August 20, 2012 10:16 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:44 PM
Points: 320, Visits: 527

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.
Post #1347557
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.