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)

------------------------------
ADDITIONAL INFORMATION:

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:

SELECT SERVERPROPERTY('Edition');

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?],
       (CASE WHEN ISNULL(OBJECTPROPERTY(p.[object_id],
                 '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;