Restoring a database to a lower edition – SQL Server 2008


>

SQL Server 2008 introduced a few Enterprise Edition-only features which changes the structure of a database when implemented. These features are:

  • Partitioning
  • Change data capture
  • Transparent data encryption
  • Data compression

Due to this, whenever you do a database restore or attach on an SQL Server 2008 instance with a lower edition, SQL Server would not allow you.

So what can you do when you have to move a database to a lower edition? Remove the offending features of course, there’s nothing else that you could do. But, how would you know which of the offending enterprise edition-only features are enabled on your database? Use the new dynamic management view sys.dm_db_persisted_sku_features. It’s a simple view which returns the feature you have enabled on your database along with its id. Hence, if the view shows any records, all you got to do now is disable the feature(s) before backing up.

Query Results

Note: Of course partitioning was present in SQL Server 2005 as well, and even there you had to remove partitioning if you had to restore a database on a lower edition.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s