2

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

4

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

  • Thank you Daniel :) We ended up realizing that the company was in fact using Enterprise version, they just didn't have it on record, until an old e-mail was pulled up. I was attempting to reload it on a personal (encrypted) system, make the changes, and load it into standard... but no need for that. – npderi Mar 10 '16 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.