Migrating a database from Enterprise to Standard Edition?

You can move or copy a database from Enterprise Edition (or Developer Edition, which supports more or less the same feature set) to Standard Edition. The simplest way is to take a backup of the database and restore that on the new server. However, if there are any Enterprise Edition features left in the database, the restored database won’t start up, and you’ll get this error, or something similar:

TITLE: Microsoft SQL Server Management Studio

Restore of database ‘databaseName’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)


Database 'databaseName' cannot be started in this edition of SQL Server because part or all of object 'myTableName' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'databaseName' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

Before you make the move, you can check for any Enterprise features in your existing database. Starting with the obvious one, check what Edition you’re actually on:


Then, check if there are any objects in the database using partitioning, compression or vardecimal:

SELECT s.[name]+'.'+o.[name] AS [object],
       i.[type_desc] COLLATE database_default+ISNULL(' '+i.[name], '') AS index_name,
       (CASE WHEN COUNT(DISTINCT p.partition_number)>1
             THEN 'Is partitioned' ELSE '' END) AS [partitioned?],
       ISNULL(MIN(NULLIF(p.data_compression_desc, 'NONE'))+
                 ' compression', '') AS [compressed?],
                 'TableHasVarDecimalStorageFormat'), 0)=0
             THEN '' ELSE 'vardecimal' END) AS [vardecimal?]
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.[object_id]=i.[object_id] AND p.index_id=i.index_id
INNER JOIN sys.objects AS o ON i.[object_id]=o.[object_id]
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
GROUP BY p.[object_id], s.[name], o.[name], i.index_id, i.[type_desc], i.[name]
ORDER BY s.[name], o.[name], i.index_id;

Finally, just to make sure, check that there aren’t any partition functions or schemes left in the database:

SELECT [name] FROM sys.partition_functions;
SELECT [name] FROM sys.partition_schemes;

2 thoughts on “Migrating a database from Enterprise to Standard Edition?

  1. Great article. Helped a lot and saved me lots of time. Time is the stuff life’s mad of. (Bend Franklin I think)

Let me hear your thoughts!

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