Sql-server – Migrate database from SQL Server 2008 Enterprise to SQL Server 2014 Standard

compressionmigrationsql server

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:

enter image description here

Best Answer

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
Related Question