Space required to restore a backup

  • Hi there,

    I've seen this message crop up a couple of times and thought nothing of it, however I'd quite like to understand the issue and how to resolve it.

    I received a database backup from a client which is a modest 396meg in size, however when I attempt to restore it, it claims to require a monstrous 99gig. The drive has almost 70gig free, plenty I would have thought for restoring a database of this size.

    Any help would be much appreciated.

  • The backup is the size of the data in the database, not the size the database was at the time of backup. To restore it, you need free space to create the data and log files the size that they were when the database was backed up.

    As an example, let's say I have a database with a 100 MB data file and a 20 log file. The data file's 25% empty. My database backup will be around 75-80 MB in size, but to restore it I would need 120MB free space on the drive.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • Generally the uncompressed backup size will be equivalent to the size of the used portion of the data and log files. When you restore, however, the full size of the data files will be required.

    restore filelistonly from disk = 'yourbackupfile.bak'

    Will tell you how big each of the files in the backup set are.

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • Rob Goddard (8/26/2009)


    Generally the uncompressed backup size will be equivalent to the size of the used portion of the data and log files.

    Used portion of data, yes. Used portion of log, no. Full backup backs just enough of the log up to ensure that the DB can be restored consistently. It's not necessarily the entire used portion of the log.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • Thanks Gail,

    Apologies for further newbish questions but I'm still struggling to get my head around the difference between the size of the data.

    Might be helpful to look at an example as I can see the difference in size but can't get my head around why.

    The database below has a size of 3.289 gig

    Whereas the resultant .bak file from a backup with all the defaults is 2.375 gig

    Although on a smaller scale, is this the same type of difference that I mentioned in my first post where the difference was 396meg and 99gig?

    Also Rob I tried your command and got the error below

    Msg 3201, Level 16, State 2, Line 1

    Cannot open backup device 'C:\Asset Management\backup.bak'. Operating system error 3(error not found).

    Msg 3013, Level 16, State 1, Line 1

    RESTORE FILELIST is terminating abnormally.

    This may be in part due to me being on SQL2005, and posting in the wrong beginner section

    Many thanks,

  • looks like there is definitely some free space in your data file(s) ..... when you try to restore it through GUI .... do you get to see the files that are there in the backup ??? if yes then restore filelist is not an issue .... something wrong with they way you are running the command as a query....

    anyways ..... I dont think you have much choice but to get to a place where you DO have space that the backup is looking for. When you see the data files in GUI suring a restire ... you could change their location to spawn different drives and that way you could have the restore working ..... you could do this at any staging server....

    Once restored ... shrink the Data and log files to release free space ..... then take another full backup of this shrinked databse and use that to restire it to your orginal intended server.

    I hope this helps ....

    -------------------------------------------------
    -Amit
    Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

  • matty_o_connor01 (8/26/2009)


    The database below has a size of 3.289 gig

    Whereas the resultant .bak file from a backup with all the defaults is 2.375 gig

    So the files in total are 3.2GB, but there's free space within the data file and the total size of the data in the files is only 2.3 GB

    Msg 3201, Level 16, State 2, Line 1

    Cannot open backup device 'C:\Asset Management\backup.bak'. Operating system error 3(error not found).

    Msg 3013, Level 16, State 1, Line 1

    RESTORE FILELIST is terminating abnormally.

    That command is valid 2000, 2005 and 2008.

    Operating system error 3 is Invalid path. Double check the path that you specified. Is it correct? Does the account that SQL server is running under have permission to read that directory?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • Amit Singh (8/26/2009)


    Once restored ... shrink the Data and log files to release free space ..... then take another full backup of this shrinked databse and use that to restire it to your orginal intended server

    Then rebuild all indexes to undo the fragmentation that the shrink caused.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • Then rebuild all indexes to undo the fragmentation that the shrink caused.

    Good point ... missed that one :satisfied:

    -------------------------------------------------
    -Amit
    Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

  • GilaMonster (8/26/2009)


    Used portion of data, yes. Used portion of log, no. Full backup backs just enough of the log up to ensure that the DB can be restored consistently. It's not necessarily the entire used portion of the log.

    Thanks Gail, I wasn't aware of that at all. Seems like such an principal thing to not know as well! :crazy:

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • Actually, of course I knew about a full backup and checkpoints in the log... What am I.. Why... - I'll get my coat.

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • Rob Goddard (9/1/2009)


    Thanks Gail, I wasn't aware of that at all. Seems like such an principal thing to not know as well! :crazy:

    Nah. It confuses lots of people.

    http://www.sqlskills.com/blogs/paul/2007/10/25/DebunkingACoupleOfMythsAroundFullDatabaseBackups.aspx

    http://www.sqlskills.com/BLOGS/PAUL/post/More-on-how-much-transaction-log-a-full-backup-includes.aspx

    Actually, of course I knew about a full backup and checkpoints in the log...

    Full backups and checkpoints?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • So, there is no other way to just restore the data without empty file space directly from the backup?

    I have the same scenario where i have database bak is 50 gb and they have allocated the data n log files in the database to be 250 GB, and when i restore it needs 250 GB and i cant restore on test with that backup because my test server has only 200GB space.

    Any way to restore just the data and remove unused space in the file while restoring from the bak file?

    thanks

  • This is what exactly i am also facing as mentioned in the below thread

    http://ask.sqlservercentral.com/questions/21893/how-can-i-restore-a-database-wo-the-free-space

  • Short answer, natively, no. You cannot restore with less than the size that existed for the data and log files at the time of the backup.

    There are third party solutions, such as Virtual Restore from Red Gate, that can help.

    Disclosure: I work for Red Gate.

Viewing 15 posts - 1 through 14 (of 14 total)

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