Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How much space does backup need?

    Hello everyone:

    I have a backup file about 9gig to be resored. The hard drive has less than 11gig space free. Is it enough for restore? Thanks.

    ZYT

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Maybe and maybe not.

    If the db has free space in the files of more that about 1 gb, you will probably fail.

    Give it a try and see. Worst case is that you fail and have to find some place else to restore it.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    betcha you got a lot of old backups eating up the drive...also betcha you use maint plans
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.

    I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.

  5. #5
    Join Date
    Mar 2003
    Posts
    223
    Quote Originally Posted by vich
    Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.

    I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.

    Hi, Vich:

    Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.

    ZYT

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by yitongzhang
    Hi, Vich:

    Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.

    ZYT
    Again; I do not know internals. I vaguely recall that the EM Restore Screen issues a "Checking for space" message while it's "thinking".

    By "Hard drive has DB only, but data files and log files are in same hard drive", I gather you mean the drive is for the DB and all DB related files (log, DB backup, etc).

    So; the very best case is that it fits. The DB may start out with a very small log file, but it will very quickly grow and then you'll be in trouble.

    But Dude; you're nuts to be squeezing your main DB disk like this unless it's just an unaviodable emergency, and even then, your emergency may become a disaster.

    For example; if your disk is highly fragmented then as free space nears it's end, even if there is theoretically enough space, the fragments may be too small. Also; you kill the chance for a decent defrag if you leave it so tight. Also; as databases and log files grow, they're sure to reach capacity quickly.

    In other words, even if by some chance it does work, not for long.

    Even with my shallow knowledge of all this, I believe that your solution will be to assign your log and backup files (for this and other databases) to another volume, preferably a dedicated volume. A simple mirrored volume is recommended. Ideally each DB will have it's own volume, but even sharing it will be better than having on the DB volume. If you have one huge production DB, you're probably best moving only it's log file there since sharing logs on volumes cause disk head thrashing that is very costly (like 10 times) to performance.

    Let me know if you want instructions for this. I had to do it recently and have it all written into scripts. The others here (far more experienced thatn I) could bless the process before proceeding.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run this command on the backup file, to see what you are dealing with:
    Code:
    restore filelistonly from disk = 'your backup file goes here'
    The output will list the size of each file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •