Releasing free (unallocated) space via backup/restore

Question

Post reply

Releasing free (unallocated) space via backup/restore

Yuri55

SSCrazy Eights

Points: 8449
More actions
November 15, 2010 at 11:43 am

#149314

I know this does not work (as just tried it 🙂 ) but just curious why not? I truncated huge table, let say 350 GB in 400 DB. Backup size certainly came down to 50 GB, but it was restored to 400 GB DB again (with unallocated space 350 GB). Only shrinking file resulted in 50 GB DB. It seems backup file "remembers" original DB space?! Any comments/clarification really appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

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

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

When did you did backup?

after shrink or before shrink.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 2

Sorry, probably was not clear.

After shrink and backup/restore I certainly got 50 GB DB

(as 350 GB unallocated space gone during shrink).

I am wondering why before shrink backup file with size 50 GB was restored to original 400 GB DB?

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 3

the space is not released untill you close session that is the reason and you made backup with out closing the session I guess.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 4

Sorry I did not get what "session" means?

First, table 350 GB size was truncated, then using RedGate Sql Backup I made backup (around 50 GB size), then using RedGate I restored DB to ... initial (before shrink) 400 GB. Not sure what is "session" here? Thanks

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

I thought you did with the script you mentioned the session.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

Check options what you had in your backup/restore tool carefully.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 7

I am afraid there is no option ( I certainly can be wrong) in restore statement (I just double checked BOL) that allows you retrieving free space during DB restoring. But instead there is probably some (hidden to me :-)) idea why it can not be done during DB restoring and this actually my question is about. Thanks

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Restore simply creates the database file the way they were when the backups was made.

If you need to shrink them after the restore, you will have to set that up yourself.

Leo.Miller SSChampion Points: 13051 More actions · Answer 9

The backup backs up the current state of the database. If you did a RESTORE HEADERONLY from BackupDevice... you will see it is expecting 400GB of space. There is no way (currently) the restore can decide that you want to shrink the unused space, and discard it when it does the restore. Also the actual data pages could be scattered all over the 400GB of files, so SQL would also need to do a reorg to sort this out. In simple terms what the backup does is:

1) Backup data pages only (indexes etc included), this is why the backup is so small.

2) Track where those are within the 400GB of files

3) Record the file names, paths, sizes so they can be recreated on restore.

When you do a restore SQL creates the same size files as were backed up and then populates them with the data pages.

If you want to remove the unused space you need to do a shrink of the files/database. Sorry, there are no short cuts.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 10

Yuri55

SSCrazy Eights

Points: 8449

November 15, 2010 at 2:58 pm

#1251016

Thanks Leo,

It seems I can not avoid shrinking