• how is the SQL database backup size estimated?

    Can it be related by any way to the original

    database size?

  • 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!



  • 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

  • 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

  • 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.


  • here are some examples for some of my databases...


    data = 57Mb

    index = 0.81Mb

    backup file size avg = 48Mb

    winzip compressed = 8Mb


    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
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • 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