Restoring database from a server that uses data compression

In our environment our production server is using MS SQL Server 2008 Enterprise Edition. We have a development and test server that runs MS SQL Server Standard Edition and the table structure between all three environments is exactly the same.

We frequently refresh our dev and test environments from our production database.

I'm looking into turning on data compression in our production environment, but reading this technet article it sounds like we will no longer be able to perform these refreshes. Am I reading this correctly?

Quote from article:

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions.

Will I be forced to upgrade our dev/test DB to Enterprise Edition to continue to refresh our data?

Correct, an error will be raised when attempting to RESTORE.

Same thing goes if other Enterprise features, such as table partitioning, are used. For those finding this answer in search, usage of Enterprise features are listed in sys.dm_db_persisted_sku_features.

What I would recommend is to use Developer Edition in your development and testing environments, if you don't support Standard in production (it sounds like your app is in-house only). This edition includes the Enterprise engine; it just isn't licenced to be used in production, and will be able to restore the Enterprise backups just fine.

StackExchange Database Administrators Q#64329, answer score: 2