Can I restore my Enterprise Edition database on a Standard edition Server?

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

When you use some SQL Server Enterprise Edition features, specifically the ones that change the way that the SQL Server Database Engine stores data, you can not restore those databases to any other edition of SQL Server than Enterprise and Developer (And Datacenter if you are using 2008 R2). The affected features are: Compression, Change Data Capture, Transparent Data Encryption and Partitioning. So, how can you know if any of your databases are using any of these features? Easy! Just execute the following script on your Enterprise Edition server, to find the database, and what feature they are using: exec sp_msforeachdb ‘select ”?”; SELECT feature_name FROM sys.dm_db_persisted_sku_features’

Then unfortunately, you will have to remove the usage of the feature, BEFORE you backup the database. After that you can restore it to say Standard Edition.