sys.dm_db_persisted_sku_features lists all features which are utilized by the database. Features specific to Enterprise/Developer edition are:

- Compression,
- Partitioning,
- TDE and CDC

These features are available only on Enterprise/Developer editions of SQL Server. You cannot attach or restore databases utilizing these features to a "lower" edition, such as Standard or Express edition.

For example, You can check features utilized by your database using:

USE SqlAndMe

SELECT      *

FROM        sys.dm_db_persisted_sku_features

Result Set:

feature_name  feature_id

Compression   100

Partitioning  200

(2 row(s) affected)

Since this database uses partition and compression it cannot be attached to an instance of "lower" edition,

If you try to attach database to a "lower" edition, you will run into below error:

Msg 909, Level 21, State 1, Line 1

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

Msg 905, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.

Msg 933, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

These error messages tell you exactly why the database cannot be started. 1st error is due to compression feature, and 2nd is for partitioning feature. Notice the last message, it says, "Database cannot be started.", the database does attach to server, but it cannot be started.

In case of restore also, you will receive same error messages, the database will be restored but, it cannot be started:

20 percent processed.

40 percent processed.

60 percent processed.

80 percent processed.

100 percent processed.

Processed 29976 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.

Processed 3 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 1.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'SqlAndMe'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Msg 909, Level 21, State 1, Line 1

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

Msg 905, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.

Msg 933, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

In order to avoid this issue, it must be planned ahead. Before using any edition-specific features ask – "Will the database need to be moved between editions?"

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)

Filed under: Backup & Recovery, Management Views and Functions, Partitioning, SQLServer, SQLServer 2008, SQLServer 2008 R2