none
Database Restore issue: There is insufficient free space on disk volume 'E:\' to create the database.

    Question

  • Guys,

    I am getting this error while restoring a database from backup file:

    'There is insufficient free space on disk volume 'E:\' to create the database. The database requires 89523748864 additional free bytes, while only 34175422464 bytes are available.'

    I have a 85GB database that has actual used size of 8-9GB, rest of the space is left unallocated/unused in log file. This means my log file is too big and has unused space nearly 72-74GB.

    I know for this i need to shrink the database and take a backup again and restore or increase the disk space and then shrink the DB.

    However, i have copied this backup for testing purpose over the network in 24 hours and don't want to repeat this. Also, i will not be able to increase my disk space. Is there somehow by chance i can get this restored, i don't need log file, just the data file is imp.

    Can i get it restored somehow?


    AMIT DALAL

    Wednesday, July 11, 2012 4:19 AM

Answers

  • Hey Amit.. You can't shrink the file while restoring it. In other words you cant restore the file only to used space. SQL Server will allocate the filesize before it start restoring the data, which means it will create the file and then do the restore. You have only two options

    *) If you have enough free space in other drive, try to restore the big file to another drive and once the restore is done. Do the shrinking and move it back to the respective data drive

    *) It's already know to you. Shrink in source, take a backup. If it's possible compress it and then copy it to the destination.

    You can also give a try with database copy wizard but Im not sure whether this will copy including the free space.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Wednesday, July 11, 2012 10:47 AM
    Moderator

All replies

  • Hi Amit,

    You can very well do it, please refer to the link here in which you can find the detailed step explain by Pradeep Adiga.

    RESTORE DATABASE YourDBName FROM DISK = 'E:\YourFileName.bak'
    WITH
           FILE = 1 ,
           RECOVERY


    Thanks & Regards, Pramilarani.R


    Wednesday, July 11, 2012 4:47 AM
  • Hi Amit,

    Yes this is very much feasible. Follow these steps:

    • RESTORE FILELISTONLY FROM DISK='YOUR_BACKUP_FILE_PATH_AND_NAME'
    • Make a note of the File ID of your .mdf file
    • RESTORE DATABASE <DBNAME> FROM DISK='YOUR_BACKUP_FILE_PATH_AND_NAME'' WITH FILE=FILEIDFROMFIRSTSTEP, RECOVERY

    Hope this will help.


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


    • Edited by GURSETHI Wednesday, July 11, 2012 4:53 AM added more details
    Wednesday, July 11, 2012 4:51 AM
  • Amit,

    you are question is right.. but usually avoid transfer of the data(i,e backup copied to network) on the network because some times the data transfer it may copy to destination but that might get corrupt again again the work will be redo .

    other way you can transfer via robocopy utility-

    about Robocopy-
    http://www.sqlservercentral.com/articles/Disaster+Recovery+%28DR%29/75860/
    http://www.sqlservercentral.com/Forums/Topic496512-357-1.aspx
    http://www.sqlmag.com/article/sql-server/tool-tip-the-awesomeness-of-robocopy


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Wednesday, July 11, 2012 9:49 AM
  • Hey Amit.. You can't shrink the file while restoring it. In other words you cant restore the file only to used space. SQL Server will allocate the filesize before it start restoring the data, which means it will create the file and then do the restore. You have only two options

    *) If you have enough free space in other drive, try to restore the big file to another drive and once the restore is done. Do the shrinking and move it back to the respective data drive

    *) It's already know to you. Shrink in source, take a backup. If it's possible compress it and then copy it to the destination.

    You can also give a try with database copy wizard but Im not sure whether this will copy including the free space.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Wednesday, July 11, 2012 10:47 AM
    Moderator