By Ashish Upadhyay
Problem:While trying to migrate the database from SQL Server 2008 Enterprise Edition to Standard Editon or Express Edition you may get the following error.
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server '***'. (Microsoft.SqlServer.SmoExtended)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database 'TFS_Configuration' cannot be started in this edition of SQL Server because part or all of object 'tbl_PropertyValue' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'TFS_Configuration' 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 reason is SQL Server Enterprise edition support "Data Compression" & "VarDecimalStorage format compression", which are not supported in SQL Server Standard and Express editon, so any database which is created in SQL Server Enterprise editon and has "compressed objects" or used "VarDecimalStorage format compression" can not move directly to any lower edition of SQL Server.
As a resolution of the problem first we need to identify and remove objects which hav compression on the database, below script will help you identifying those objects.
Run the below script to get list of objects which has data compression.
SCHEMA_NAME(sys.objects.schema_id) AS SchemaName
,OBJECT_NAME(sys.objects.object_id) AS ObjectName
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
Now run the below command for each objects from the above result set to see if the object has VarDecimalStorage format compression, zero means not dose not have.
Finally run the below command to rebuild the indexes with Data compression option as 'None' in order to disable the compression. You need to run this command for each object(s) return by the first query.
ALTER INDEX ALL ON tbl_PropertyValue REBUILD WITH (DATA_COMPRESSION = None);
Now you can backup your database from SQL Server Enterprise and restore on SQL Server Standard/Express.
I guess this is going to help.