Migrate database from SQL Server 2008 Enterprise to SQL Server 2014 Standard

Question

I need move database with "data compression" from SQL Server 2008 Enterprise to SQL Server 2014 Standard.

But Standard edition does not support "data compression".

How can I disable "data compression" in database?

I tried this:

ALTER INDEX [_Acc13_ByCode_SR] ON [dbo].[_Acc13] 
   REBUILD PARTITION = ALL
   WITH (DATA_COMPRESSION = NONE)

but still see that compression is enabled:

Answer 1

I need move database with "data compression" from MS SQL 2008 Enterprise to MS SQL 2014 standard.

Downgrading is always a pain !

If you have a source control for your database, I would suggest you to create the database shell (schema, objects - tables, views, indexes) on the new 2014 standard server.

BCP out and BULK INSERT from the old server (Enterprise edition) to New 2014 (standard edition) server.

I would say, rely on T-SQL rather than GUI. From Disable Compression on a Table or Index

-- To disable compression on a table

USE AdventureWorks2012;
GO
ALTER TABLE Person.Person REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);
GO

-- To disable compression on an index

USE AdventureWorks2012;
GO
ALTER INDEX AK_Person_rowguid ON Person.Person REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
GO

Answer 2

You could always script out the relevant tables and/or indexes by using the following statements. They create the relevant ALTER TABLE ... or ALTER INDEX ... statements which you can then cut & paste from the output window to the input window and use to reset the compression to NONE.

They aren't the prettiest, but they do the job.

Table objects

SELECT 'ALTER TABLE [' + ss.name + '].[' + so.name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO'
       /*,
       so.name,
       ss.name,
       so.xtype,
       so.uid,
       so.status,
       sp.data_compression_desc*/
FROM   sys.partitions       AS sp
       JOIN sys.objects  AS so
            ON  sp.[object_id] = so.[object_id]
       JOIN sys.schemas     AS ss
            ON  so.[schema_id] = ss.[schema_id]
WHERE  1 = 1
       AND sp.data_compression_desc != N'NONE'

The ouput will look a bit like this (TEXT):

ALTER TABLE [sys].[syscsrowgroups] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
ALTER TABLE [sys].[syscscolsegments] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
ALTER TABLE [sys].[syscsdictionaries] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
ALTER TABLE [DBT].[Database] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO

Index objects

SELECT 'ALTER INDEX [' + si.name + '] ON [' + ss.name + '].[' + so.name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO'
       /*>,
       si.name,
       so.name,
       ss.name,
       sp.data_compression_desc*/
FROM   sys.indexes          AS si
       JOIN sys.partitions  AS sp
            ON  si.[object_id] = sp.[object_id]
            AND sp.index_id = si.index_id
       JOIN sys.objects  AS so
            ON  so.[object_id] = si.[object_id]
       JOIN sys.schemas     AS ss
            ON  so.[schema_id] = ss.[schema_id]
WHERE  1 = 1
       AND sp.data_compression_desc != N'NONE' 

The output will look a bit like this (TEXT):

ALTER INDEX [clust] ON [sys].[syscsrowgroups] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
ALTER INDEX [clust] ON [sys].[syscscolsegments] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
ALTER INDEX [clust] ON [sys].[syscsdictionaries] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
ALTER INDEX [CL_UX_Database_ID_DB_NAME_INST_ID] ON [DBT].[Database] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO

You can build upon these scripts and add in all the relevant options for the ALTER INDEX statement.

Caution: You need enough free space in the database for the decompress to work.

Estimate size of decompressed data

With the following script you can create an output which you can then use to query all the objects that have compression turned on:

SELECT 'sp_estimate_data_compression_savings @schema_name = ''' + ss.name + ''', @object_name = ''' + so.name + 
       ''', @index_id = ' + CAST(si.index_id AS NVARCHAR(100)) + 
       ', @partition_number = ' + CAST(sp.partition_number AS NVARCHAR(100)) + ' , @data_compression = ''NONE''
GO'
FROM   sys.indexes          AS si
       JOIN sys.partitions  AS sp
            ON  si.[object_id] = sp.[object_id]
            AND sp.index_id = si.index_id
       JOIN sys.objects     AS so
            ON  so.[object_id] = si.[object_id]
       JOIN sys.schemas     AS ss
            ON  so.[schema_id] = ss.[schema_id]
WHERE  sp.data_compression_desc != N'NONE'

...which will generate scripts that look at bit like these examples:

sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'IDXProperty2', @index_id = 1, @partition_number = 1 , @data_compression = 'NONE'
GO
sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'IDXProperty6', @index_id = 1, @partition_number = 1 , @data_compression = 'NONE'
GO

And when you run the above statements against your database, you will get a summary of the expected decompressed data for each object. Following an example output for an object:

Good luck!