Posted by decipherinfosys on July 11, 2009
SQL Server 2008 has a new DMV which you might need if you run into an issue similar to what we ran into last week. At one of our client sites, they were using CDC and data compression features in SQL Server 2008. When they tried to restore the database in their development environment, the restore operation failed. The reason is that for certain features in SQL Server 2008 which are only supported in the Enterprise Edition, if you are using those features in your environment and you try to restore a backup of such a database on an instance that does not support those features, you will not be able to do so. This is written in BOL as well:
Some features of the SQL Server Database Engine change the way that Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features cannot be moved to an edition of SQL Server that does not support them Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.
There are four features that fall into this category: CDC (Change Data Capture), Data Compression, TDE (Transparent Data Encryption) and partitioning. It actually was the same issue in SQL Server 2005 as well when you would have used the partitioning feature. In such scenarios, you can use the above mentioned DMV to see whether you are using any of those four features.