Can I Restore My Enterprise Edition Database on All Editions of SQL Server?

Q: I’m designing a disaster recovery strategy for SQL Server 2008 and want to make sure I’ve got an alternative server to restore my critical databases on. I’d prefer to use Standard Edition to save on licensing costs, but will it be a problem if I’m using Enterprise Edition for the production server?

A: Yes, this might be a problem. In SQL Server 2005 and later, some Enterprise Edition–only features make a database Enterprise Edition–only (i.e., it can’t be restored on a Standard Edition or lower SQL Server system) if the features are present in the database.

In SQL Server 2005, the only feature to which this rule applies is table/index partitioning. There’s no easy way to tell whether you have partitioning in a database without querying the storage metadata to see if any tables/indexes have multiple partitions.

In SQL Server 2008, there are four features to which this rule applies:

1.     table/index partitioning

2.     transparent data encryption

3.     change data capture

4.     data compression

Figure 1 shows an example of an error message you’d receive when trying to restore a SQL Server 2008 database with data compression to a SQL Server Express instance using the following code:

RESTORE DATABASE EnterpriseOnly FROM DISK = 'C:SQLskillsPaulsEnterpriseOnlyDB.bck'GO

Figure 1: Error message returned when attempting to restore a SQL Server 2008 database with data compression to a SQL Server Express instance

RESTORE DATABASE EnterpriseOnly FROM DISK = 'C:SQLskillsEnterpriseOnly.bck'GOProcessed 160 pages for database 'EnterpriseOnly', file 'EnterpriseOnly' on file 1.Processed 5 pages for database 'EnterpriseOnly', file 'EnterpriseOnly_log' on file 1.Msg 3167, Level 16, State 1, Line 1RESTORE could not start database 'EnterpriseOnly'.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.Msg 909, Level 21, State 1, Line 1Database 'EnterpriseOnly' cannot be started in this edition of SQL Server because part or all of object 'compressed' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.Msg 933, Level 21, State 1, Line 1Database 'EnterpriseOnly' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Because SQL Server 2008 contains more features that can trip you up, it includes a Dynamic Management View (DMV) called sys.dm_db_persisted_sku_features that will tell you if you have any of these features in your database. For example, if you run the code

SELECT * FROM sys.dm_db_persisted_sku_features;

GO

you’ll receive the following output, which shows you have data compression enabled:

feature_name    feature_id

--------------  -----------

Compression     100

Other than data compression, all of the SQL Server 2008 R2 and SQL Server 2008 Enterprise Edition features that make a database Enterprise Edition–only require essentially database owner (DBO) or systems admin permissions, but data compression requires only the ALTER TABLE permission on the table involved. This means a table owner can make a database Enterprise Edition–only without the DBA realizing it. Therefore, you must make sure that no one can enable these features without you knowing it if you want to be able to restore your database to a non-Enterprise Edition SQL Server instance.