How to disable SQL Server Enterprise Edition's data compression in preparation for migrating to a lower version of SQL
This has been probably one of the most common issues I have come across in the past as it relates to Tfs data migration and or upgrade.
Scenario #1: You may be upgrading Tfs on new hardware and don't have the same version of SQL Server Enterprise in production.
Scenario #2: You have done your proof of concept in a testing environment with a Enterprise edition SQL Server, and now need to move to production where you only have SQL Server Standard Edition
Whichever the scenario, here is a quick solution that MUST be executed prior to the database backup, copy, restore steps.
Run the following command against the SQL Server Enterprise instance.
EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1
Please do not hesitate to share you experience.
Cheers!