Post reply

Restore a database with a used space only

  • May 22, 2012 at 1:16 pm

    #258523

    Hi everybody,

    I need to restore a SQL Server database that is 60GB and have 75% free space is set with the Initial Size of 58GB with Autogroth of 2GB.

    Is there a way to Recover just the used space so the database size would be 10GB instead of 60?

    Environment: SQL Server 2008 Standard 64bit; Operating System: Windows Server 2008 R2 Enterprise.

    Simple Recovery model (but can switch it if needed)

    Any help and cooperation would be greatly appreciated.

    Alex

  • May 22, 2012 at 1:20 pm

    #1491333

    not natively no. if the database is set to 60 gig, even though it's 10% used, you need 60 gig to restore it.

    that said, Redgates SQL virtual restore will allow you to open a full backup exactly as if it were a normal database, and without the need for the whole extra space thing.

    you can test drive it off of their web site, but for me, it's one of those apps that are worth the money.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 22, 2012 at 1:41 pm

    #1491350

    You can should be able to reset the initial size to 10GB, then use the SHRINKDATABASE function to shrink the file & reclaim free space.

    Some advise against ever shrinking the database because it causes index fragmentation, so you should be prepared to rebuild the indexes. It sounds like a lot of data had been deleted, so you'd probably beneft from a full index rebuild anyway.

Viewing 3 posts - 1 through 2 (of 2 total)

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