Post reply

Restore of DB without the empty space bloat

  • February 15, 2011 at 1:41 pm

    #246701

    Hey guys is there an easier way to do this?

    I have a 250gb db with 150gb in a transaction table. Development wants copies of the db occasionally but without the big table. If I drop the table I am left with a db with 150gb of empty space. If I do a shrink file on the mdf file it will do the shrink at a little less than 1 gb per minute or 150 minutes plus. I can backup the original database (250gb) in about 35 minutes with a restore of about the same time.

    Does anyone know of a way to drop the table, do a backup and then do a restore, getting around restoring in a file size/space of 250 gb but in a size/space of 100gb instead?

    I know there are other issues with this setup but my hands are tied for the most part, so this is what I have to work with :crying:

    Any help would be appreciated.

    Thanks,

    John.

  • February 15, 2011 at 1:48 pm

    #1287306

    Restore will recreate the database exactly as it was at the time of the backup. Free space and database file size included.

    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

  • February 15, 2011 at 2:02 pm

    #1287310

    i fiddled briefly with the trial version of Idera's Virtual Database, which some how magically opens a Full Backup as if it were real, without grabbing disk space, and even commits changes to that database that you make.

    I saw that would be ideal for exactly this situation...room for the backup, but not the restore., and can actually work with the db itself.

    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!

  • February 15, 2011 at 7:21 pm

    #1287422

    Red Gate's SQL Virtual Restore[/url] is another option you could consider. It allows you to mount a database from a backup file, without needing to actually perform a restore, and gives you read/write access to the mounted database.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • February 16, 2011 at 5:39 am

    #1287581

    Exactly Gail,

    But you never know if you are missing something:-)

Viewing 5 posts - 1 through 4 (of 4 total)

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