SQL Server – Error 909 Restoring a Database on Standard Edition

errorsrestoresql serversql-server-2012

I ran into a problem with SQL Server 2012 Standard Edition (64-bit).

I have rebuilt a system with the exact same specifications for software as it had been built previously (Windows 2012, SQL Server 2012 Standard Edition). Loading the majority of the databases seem to cause no issues, with the exception of just a few that cause the following error when attempting to restore:

Database 'xxx' cannot be started in this edition of SQL Server because part or all of object 'Country' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

Database 'xxx' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

The previous version of SQL Server was Standard as well. Short of uninstalling and re-installing the Enterprise version, is there a solution? I did some searching, and tried the following, but it doesn't really want to do anything to the database other than to set it to "emergency mode".

Recovering a SQL Server Database from Suspect Mode

Best Answer

According to the error message, your database contains Enterprise Edition features in the table Country. First of all, check the following on the original (old) server:

SELECT SERVERPROPERTY('Edition');

That should tell you if you're running on Standard, Enterprise or Developer Edition. Developer Edition supports all of the Enterprise features (with different licensing terms). Could that be the issue?

Check if compression, partitioning or vardecimal is enabled on any object:

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, see if there are any partition functions or partition schemes that you haven't cleaned out:

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

You may also consult the following link (originally provided by Shanky in a comment):

How to identify compressed tables before restoring/migrating a database to any edition other than Enterprise Edition of SQL Server 2008

Related Question