HOW TO IDENTIFY COMPRESSED TABLES BEFORE RESTORING/MIGRATING DATABASE to any edition other than Enterprise Edition of SQL Server 2008

 PROBLEM DESCRIPTION

 ===================

While trying to migrate the database from sql2008 enterprise to standard you get the following error:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Restore failed for Server ‘SQL_SERVER’. (Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Database 'DB1' cannot be started in this edition of SQL Server because part or all of object 'object1' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

Database 'DB1’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

CAUSE

=======

We cannot restore a back up of a database which has compressed objects in sql2008 Standard edition because data compression is only supported in Enterprise edition.

https://technet.microsoft.com/en-us/library/cc645993(SQL.100).aspx

RESOLUTION

===========

àOn an Enterprise Edition, we first need to identify and then remove the objects which have compression on the database.

à Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data compression:

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]

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'

ORDER BY SchemaName, ObjectName

àTo check for vardecimalstorage format compression run the following command:

SELECT OBJECTPROPERTY(OBJECT_ID(‘<object name(s) from above command output>’),

  'TableHasVarDecimalStorageFormat') ;

GO

à For each object that is reported by the above select query you need to disable the compression by using the following command

ALTER INDEX ALL ON <TABLE NAME>

REBUILD WITH (DATA_COMPRESSION = None);

 àRe-run the script to check if any compression is still there

à If not back up the database in SQL2008 Enterprise and now the restore will succeed on a lower edition.

Additional Information

===================

SKU persisted features would not work across SQL Editions unless the edition supports it.

Refer: sys.dm_db_persisted_sku_features

The above article has more details regarded SKU persisted features.

Sakshi Jain

Support Engineer, Microsoft SQL Server PSS

Reviewed by
Sudarshan Narasimhan, Shamik Ghosh
TL, Microsoft SQL Server PSS