While trying to migrate the database from sql2008 enterprise to standard you get the following error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server ‘SQL_SERVER’. (Microsoft.SqlServer.SmoExtended)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database 'DB1' cannot be started in this edition of SQL Server because part or all of object 'object1' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'DB1’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)
We cannot restore a back up of a database which has compressed objects in sql2008 Standard edition because data compression is only supported in Enterprise edition.
àOn an Enterprise Edition, we first need to identify and then remove the objects which have compression on the database.
à Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data compression:
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[index_id] as [IndexID_on_Table]
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName
àTo check for vardecimalstorage format compression run the following command:
SELECT OBJECTPROPERTY(OBJECT_ID(‘<object name(s) from above command output>’),
à For each object that is reported by the above select query you need to disable the compression by using the following command
ALTER INDEX ALL ON <TABLE NAME>
REBUILD WITH (DATA_COMPRESSION = None);
àRe-run the script to check if any compression is still there
à If not back up the database in SQL2008 Enterprise and now the restore will succeed on a lower edition.
SKU persisted features would not work across SQL Editions unless the edition supports it.
The above article has more details regarded SKU persisted features.
Support Engineer, Microsoft SQL Server PSS
Reviewed by Sudarshan Narasimhan, Shamik Ghosh TL, Microsoft SQL Server PSS
You additionally need to execute
ALTER TABLE [table_name] REBUILD WITH (DATA_COMPRESSION = NONE);
for the Heaps.
Thanks for this great info. I have about 50.000 objects in database and it's hard to find the object with SELECT OBJECTPROPERTY one by one. Is there another way to solve this issue?