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!

Comments

• Anonymous
  December 19, 2013
  The comment has been removed
• Anonymous
  September 02, 2015
  This procedure can be used only on TFS databases. If you want to disable copmpression for other DB you have to do something like this: SELECT SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] ,[rows] ,[data_compression_desc] ,[index_id] as [IndexID_on_Table] into #compr FROM sys.partitions INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id WHERE data_compression > 0 AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' while exists(select 1 from #compr) begin       declare @i varchar(200) = (select top 1 [ObjectName] from #compr)       print @i       exec ('ALTER INDEX ALL ON '+@i + ' REBUILD WITH (DATA_COMPRESSION = None)')       delete from #compr where [ObjectName] = @i end drop table #compr