October 7, 2011 at 8:15 pm
Hello,
I was just curious if executing "backing database with compression" causes any index fragmentation to the .bak file? I would hate to see a well maintained database restored to another server be highly fragmented, which would require an index rebuild.
Thanks
October 7, 2011 at 8:36 pm
To the best of my knowledge backup with compression will not cause any fragmentation. It is a compressed copy of the database in the form it was in when backed up.
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 7, 2011 at 9:24 pm
Thanks for confirming that for me Jack. Just wanted to double check before I use this feature.
October 7, 2011 at 9:29 pm
No worries. I did a quick test where I stored results of sys.dm_db_index_physical_stats, did a compressed backup, restored the backup, and did another query of sys.dm_db_index_physical_stats and ended up with the same results.
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 7, 2011 at 9:36 pm
Gotta say thanks once again for going above and beyond Jack. Appreciate that you ran the tests to confirm the results 😀
October 8, 2011 at 4:29 am
A restore will always recreate the databae exactly as it was at time of backup. No alterations, no layout changes, nothing.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply