In my continuing effort to bring forth the lesser known new features in SQL Server 2008 I give to you:
sys.dm_db_persisted_sku_features
This is great. This new DMV gives you a nice quick view of the features that your database uses that are restricted to only Enterprise or Developer editions of SQL Server. Some examples of this (listed in BOL) are:
Compression. Indicates that at least one table or index uses data compression or the vardecimal storage format.
Partitioning. Indicates that the database contains partitioned tables, partitioned indexes, partition schemes, or partition functions.
TransparentDataEncryption. Indicates that a database is encrypted by using transparent data encryption.
ChangeCapture. Indicates that a database has change data capture enabled.
Below is some code to
Create a Test Database
SELECT from sys.dm_db_persisted_sku_features
Enable Change Data Capture
SELECT from sys.dm_db_persisted_sku_features
Drop the Test Database
CREATE DATABASE TestDatabase
GO
USE TestDatabase
GO
SELECT * FROM sys.dm_db_persisted_sku_features;
GO
EXEC sys.sp_cdc_enable_db
GO
SELECT * FROM sys.dm_db_persisted_sku_features;
GO
USE MASTER
GO
DROP DATABASE TestDatabase
So if you’re looking to backup a database from an Enterprise or Developer instance and restore it to a instance that is not Enterprise or Developer edition have a look at this DMV and potentially save yourself some headaches.
Enjoy!!!
