Deans Blog

I was recently attempting to restore a database created on Microsoft SQL Server Enterprise Edition to an instance running Microsoft SQL Server Standard Edition, when the following error was reported on invoking the restore of the database.

Restore failed for Server ‘<SQL Server>’. (Microsoft.SqlServer.SmoExtended)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database  <database name> cannot be started in this edition of SQL Server because part or all of object ‘<object>’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database <database name> 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 cause of the issue is that Microsoft SQL Server Standard Edition does not support compressed objects. Therefore, I was required to identify the compressed objects on the source database to which the backup was created by invoking the below script agaisnt the source database.

SELECT 
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] 
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] 
,[rows] 
,[data_compression_desc] 
,[index_id] as [IndexID_on_Table]
FROM sys.partitions 
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

The above should return a list of the compressed objects in the database, to which we will now run the below script to determine the table names to which  vardecimalstorage format compression is enabled on each object returned.

SELECT OBJECTPROPERTY(OBJECT_ID(‘<OBJECT>’),
 'TableHasVarDecimalStorageFormat') ;
GO

For each table name returned, we will now alter the index and rebuild with data compression disabled. Once the rebuild has completed I was able to restore the database to a Microsoft SQL Server Standard Edition instance.

ALTER INDEX ALL ON <TABLE NAME>
REBUILD WITH (DATA_COMPRESSION = None);