Post reply

Restore database from a compressed backup (data not compressed) to compress the data at restore time

  • August 17, 2015 at 11:53 am

    #320646

    Hello:

    Is it possible to compress the data at restore time?.

    Backup is coming from a not compressed database.

    The backup file is compressed.

    Can it be done?

    Thank you in advance

  • August 17, 2015 at 12:10 pm

    #1820534

    no, sorry.

    if the database was 150 gig at backup time, you need 150 gig of disk space to restore it, regardless of the size of the *.bak file itself.

    the database could be empty, but reserving 150 gig for future use; you need the disk space to restore the database.

    I thought that a maybe/might work solution that might work, where you mark a folder as compressed,and try to restore the file inside that folder. I've never tried, and just found an explicit article that says SQL will allow it.http://en.community.dell.com/techcenter/data-protection/b/weblog/archive/2012/07/05/restore-a-database-as-compressed-a-how-to-guide

    you'd be best off just clearing some disk space to restore, rather than trying to restore to a compressed folder

    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!

  • August 17, 2015 at 1:39 pm

    #1820567

    Thank you....

  • August 17, 2015 at 2:19 pm

    #1820581

    Find a host with lots of cheap disk and restore there, and shrink, and backup to get a good bak file.

    Check the autogrowth settings while you're at it. I've had wiseguys backup a small, shrinked full database which had a 100GB autogrow. After restoring, the first update I did tried to allocate all that space and ran out of room.

  • August 18, 2015 at 12:16 pm

    #1820851

    Large data files are typically not an issue, assuming IFI is on. But the log file must always be preformatted. If possible, you'll want to reduce the log file size on the backup. Edit: That is, shrink the log if necessary before the backup that will be restored is taken.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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