Backup size

  • caz100

    SSCommitted

    Points: 1808

    Hi

    Is it possible to have a database size showing 75 gigs and then when you do a copy only backup without compression the backup size is less than 5 gigs.

     

    The reason I ask is the other day I had a db with 80gigs in size in db properties and the copy only back was 70gigs in size!

  • jasona.work

    SSC-Forever

    Points: 49996

    Yes, because the backup is only the actual data in the database, as well as enough info to re-create the database files.  So you could have a 1TB size-on-disk but if there's only a couple hundred MB of data, you're going to have a small backup file.

    Copy-only or not doesn't make a difference, the only thing the copy-only does is not reset the point that log and differential backups will be built from.

  • caz100

    SSCommitted

    Points: 1808

    • Ok thanks ...I was starting to worry one of them one must be wrong.
  • Jeff Moden

    SSC Guru

    Points: 997311

    caz100 wrote:

     

      <li style="list-style-type: none;">

    • Ok thanks ...I was starting to worry one of them one must be wrong.

     

    Heh... you still might have a problem.  Compression works well but normally not that well.  If your database compresses to only 1/40th of the size during a backup, you may have a shedload of "unused" but allocated space in your database that needs to be cleaned up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • caz100

    SSCommitted

    Points: 1808

    Ok is there anyway to predict the backup size so I  know if my (80 db size in db properties) with a 5 gigs backup without using compression is ok?

  • caz100

    SSCommitted

    Points: 1808

    And to add....

    Datafile of 50 gigs 40% free space and log file of 30 gigs 99% free.....so 5 gigs might be ok for backup ?

  • as1981

    SSCrazy

    Points: 2774

    Firstly I'm only a Junior/mid level DBA so I could be wrong with this.

    Secondly I appreciate it's a copy-only backup so perhaps not part of your normal backup strategy.

    Your questions make me wonder whether you test your regular backups. My understanding (and remember I'm only a junior DBA) is that it's advisable to do this quite regularly. It's possible to get caught in "SQL server/SQL agent says the backups complete so it's everything is fine" when actually there is an issue. SQL agent server/agent isn't being inaccurate it just doesn't cover all scenairos.

    Apologies if I'm being patronising or if I've missed the point. Also apologises for going off tangent to your question.

  • Jeffrey Williams

    SSC Guru

    Points: 88699

    caz100 wrote:

    And to add....

    Datafile of 50 gigs 40% free space and log file of 30 gigs 99% free.....so 5 gigs might be ok for backup ?

    50 GB at 40% free puts the used size around 30GB (20GB free).  Compressing 30GB to 5GB is entirely possible...

    To validate your backup, you need to restore it - that is the only way to validate any backup.  Take that backup file and copy it to another server and try to restore it to another instance of SQL Server.  At this size, you can test the restore on a personal workstation as long as you have enough storage for the expanded files.  Remember, you are only testing that you can successfully restore the backup and bring that database online.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 8 posts - 1 through 8 (of 8 total)

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