Microsoft SQL Server: Error: 909 while migrating a Database from SQL Server Enterprise Edition of SQL Server 2008 to any lower Edition

By Ashish Upadhyay

Problem:

While trying to migrate the database from SQL Server 2008 Enterprise Edition to Standard Editon or Express Edition you may get the following error.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server '***'.  (Microsoft.SqlServer.SmoExtended)

------------------------------
ADDITIONAL INFORMATION:

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

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

Database 'TFS_Configuration' cannot be started in this edition of SQL Server because part or all of object 'tbl_PropertyValue' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'TFS_Configuration' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

Reason:

The reason is SQL Server Enterprise edition support "Data Compression" & "VarDecimalStorage format compression", which are not supported in SQL Server Standard and Express editon, so any database which is created in SQL Server Enterprise editon and has "compressed objects" or used "VarDecimalStorage format compression" can not move directly to any lower edition of SQL Server. 

Solution:

As a resolution of the problem first we need to identify and remove objects which hav compression on the database, below script will help you identifying those objects. 

Run  the below script to get list of objects which has data compression.

SELECT

 SCHEMA_NAME(sys.objects.schema_id) AS SchemaName

,OBJECT_NAME(sys.objects.object_id) AS ObjectName

,[rows]

,[data_compression_desc]

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

Now run the below command for each objects from the above result set to see if the object has  VarDecimalStorage format compression, zero means not dose not have.

SELECT OBJECTPROPERTY(OBJECT_ID('tbl_PropertyValue'),'TableHasVarDecimalStorageFormat');

Finally run the below command to rebuild the indexes with Data compression option as 'None' in order to disable the compression. You need to run this command for each object(s) return by the first query.

ALTER INDEX ALL ON tbl_PropertyValue REBUILD WITH (DATA_COMPRESSION = None);

Now you can backup your database from SQL Server Enterprise and restore on SQL Server Standard/Express. 

I guess this is going to help.