January 15, 2002 at 4:44 am
how is the SQL database backup size estimated?
Can it be related by any way to the original
database size?
January 15, 2002 at 5:21 am
Would depend on the amount of free space in the db, it doesnt get backed up. I think there is definitely a relationship, though I dont expect it will exact.
Anyone got a query to figure out the ratio? I've got 250 db's I could test on, probably get some others to do as well, see how it comes out. Might make an interesting short article!
Steve?
Andy
January 15, 2002 at 10:17 am
Nice easy one this...
Run sp_spaceused from the database in question. The "Reserved Space" plus 1-2% will give you a pretty good approximation of full backup size.
Hope this helps,
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
January 15, 2002 at 10:20 am
I've also got a script which will either print space used etc. to results window or dump it to a table. Quite useful for monitoring incremental database growth. Let me know if anyone wants a copy or where to submit it.
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
January 15, 2002 at 3:36 pm
I was thinking of one that would not just get free space, but would look at the size of the most recent backup on disk and show the compression ration after excluding free space.
Andy
January 16, 2002 at 2:59 am
here are some examples for some of my databases...
DB1
data = 57Mb
index = 0.81Mb
backup file size avg = 48Mb
winzip compressed = 8Mb
DB2
data = 1.7Gb
index = 158Mb
backup file size avg = 916Mb
winzip compressed = 143Mb
so you can work out the averages from there.. 2 DB is not much to average against though and doesnt consider ALU size.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 21, 2002 at 10:13 am
Not sure if you can reliably get this, but I'd try some of the suggestions above. Let us know!
Steve Jones
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply