October 26, 2011 at 1:41 pm
I know that database physical files can be fragmented especially when growing in small autogrow increments. When you backup a database does that physical file fragmentation stay in the backup file? I know that index fragmentation will remain.
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 26, 2011 at 1:53 pm
no.
if you restore the backup to somewhere with contiguous space, the data file will be contiguous.
So the fragmentation of the file after restore depends on the free space of the target drive.
---------------------------------------------------------------------
October 26, 2011 at 2:04 pm
Thanks George. That's what I thought, but I needed to get some confirmation. Sometimes what I think is logical and reasonable result is not right.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 26, 2011 at 3:23 pm
george sibbald (10/26/2011)
no.if you restore the backup to somewhere with contiguous space, the data file will be contiguous.
So the fragmentation of the file after restore depends on the free space of the target drive.
What happens if you restore by overwriting an old DB (previous PIT), which is heavily fragmented on the hd?
October 26, 2011 at 3:37 pm
Ninja's_RGR'us (10/26/2011)
george sibbald (10/26/2011)
no.if you restore the backup to somewhere with contiguous space, the data file will be contiguous.
So the fragmentation of the file after restore depends on the free space of the target drive.
What happens if you restore by overwriting an old DB (previous PIT), which is heavily fragmented on the hd?
Good question. Also how do you verify the fragmentation or lack thereof of the file(s)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 26, 2011 at 3:48 pm
Ninja's_RGR'us (10/26/2011)
george sibbald (10/26/2011)
no.if you restore the backup to somewhere with contiguous space, the data file will be contiguous.
So the fragmentation of the file after restore depends on the free space of the target drive.
What happens if you restore by overwriting an old DB (previous PIT), which is heavily fragmented on the hd?
Hmm. thats a different question. the original question was is the fragmentation in the backup file, which it isn't. Restoring over an existing database I don't know, but there is no guarantee the OS will directly overwrite the file, they might not be the same size. You would hope the OS will try to make it contiguous.
---------------------------------------------------------------------
October 26, 2011 at 4:00 pm
As for identifying fragmentation the windows defrag tool can obviously do that, and tools such as diskkeeper will give you more options.
I am not sure if they tell you which fragments are which file, but you could certainly identify the overall level of fragmentation on the drive before and after
---------------------------------------------------------------------
October 27, 2011 at 5:38 am
This is just my guess here as I don't know those internals.
Assuming that the file's definition don't change, I wouldn't expect the server to overwrite anything. I mean soemthing like delete the file, then make a new one.
I'd expect the file to stay intact windows side, then having the server just overwrite the pages inside those files... meaning the physical fragmentation won't change.
Here's how I'd test it out.
Create a new db, autogrow at 1 mb. Run inserts for like 10-15 minutes in a loop.
While that happens either run another loop for another db or maybe download 5-15 big files from ms.com. That should create plenty of disk fragmentation.
Take backup.
Run defrag with sql server on (to defrag everything else).
Shutdown the server, run defrag again to see the before & after picture.
Start the server, restore the db(s).
Shutdown again & defrag again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply