locked
How can i restore a databes with only used data without creating the actual size of the data files at the destination RRS feed

  • Question

  • I want to restore a database only with the actual data used.
    I do not want the restore to create the actual size of the files defines at the source.

    Can someone share this trick?

     

    Friday, July 1, 2011 8:45 AM

Answers

  • Hi,

    By default, after you restored a database, the new created database will be created with the same database size as source database no matter how much the actual data is stored. If you want the size of your new created database is similar with actual data used, you need to shrink the database. In some scenarios, if you have large with dozens of GBs or even TBs but with fewer actual data in, I would recommend you have enabled "Instant File Initialization" feature for SQL Server so that there is no need to zeroed the whole data files during the installation which can reduce the time for restoring largely.

    As a side note, shrinking database can decrease the whole database performance, so that you may need to defrag after shrinking. Here is a great blog post on it, see: http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx.

    Hope this helps.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by VidhyaSagar Friday, July 8, 2011 3:58 PM
    • Marked as answer by Stephanie Lv Monday, July 11, 2011 9:35 AM
    Tuesday, July 5, 2011 8:41 AM

All replies

  • What do you mean by "only with the actual data used".  If the data is in the database, its being used.

    If you only want to use a subset of the data, you will need to export that data into another database


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Friday, July 1, 2011 10:37 AM
  • Hi Namit,

     

    Can you please explain your requirement in detail?

     

    Best regards,

    Sengwa


    Best regards, Sengwa
    Tuesday, July 5, 2011 3:32 AM
  • Hi,

    By default, after you restored a database, the new created database will be created with the same database size as source database no matter how much the actual data is stored. If you want the size of your new created database is similar with actual data used, you need to shrink the database. In some scenarios, if you have large with dozens of GBs or even TBs but with fewer actual data in, I would recommend you have enabled "Instant File Initialization" feature for SQL Server so that there is no need to zeroed the whole data files during the installation which can reduce the time for restoring largely.

    As a side note, shrinking database can decrease the whole database performance, so that you may need to defrag after shrinking. Here is a great blog post on it, see: http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx.

    Hope this helps.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by VidhyaSagar Friday, July 8, 2011 3:58 PM
    • Marked as answer by Stephanie Lv Monday, July 11, 2011 9:35 AM
    Tuesday, July 5, 2011 8:41 AM
  • It is not possible via tools of MS..As Alex pointed RESTORE requires space needed for database..You can put 'important' tables onto different filegroups and restore only them... Take a look at piece restore in the BOL
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 6, 2011 7:58 AM
  • you may need to shrink databse before backup, this would help.

     

     

     


    -Giri
    Wednesday, July 6, 2011 8:34 PM
  • Giri

    Why to shrink?If you have empty pages it will not be included in backup, I mean if your data file is 100 gb , that is not fact that bak file will be in the same size..


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 7, 2011 5:41 AM
  • restore the entire database and

    delete those that you dont need

    or

    restore the database to a staging server

    and copy only what you want.

    when your done delete the database from the staging server


    http://joeydj.com
    Thursday, July 7, 2011 2:54 PM
  • Hi Uri

    I agree with you on backup process it will backup only used pages, but if you are using tools like Veritas Netbackup it goes against file, we shrink file before backup to reduce file size on backup media server. I appreciate rising good point.

     

    -Giri 


    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Friday, July 8, 2011 1:33 PM
  • I too am looking for an answer to this. MS SQL 2008

    I have a 20 Gig DB and only 2-3 Gig in use.

    An option to restore the DB using only 2-3 G is far more sensible in the particular case I am working on.

    The backup is only 2 Gig so I want my restored database do be around this figure too.

    Any help appreciated.

    Thanks

    Ian

     

    Thursday, July 21, 2011 12:34 AM
  • Do we have any solution for this?

    The people who have written above do not make any sense as everyone knows about shrink / restore process.

     

    I am looking for a restore command which does not allocate the datafile size as in from source.

     

     

    Monday, August 1, 2011 6:18 AM