Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup size Expand / Collapse
Author
Message
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: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
Hello,

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

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Posted Monday, August 20, 2012 4:03 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 632, Visits: 2,950
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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1347500
Posted Monday, August 20, 2012 4:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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
Posted Monday, August 20, 2012 6:00 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 632, Visits: 2,950
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1347518
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: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078

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