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.
Thanks for sharing this informative article with us, I have found another article on the same error. Must read from here: http://sqltechtips.blogspot.com/2016/08/sql-server-2008-error-909.htmlReplyDelete
Great Article Artificial Intelligence ProjectsDelete
Project Center in Chennai
See, to look for specific data within a Microsoft Excel Database or Excel List we do not use the common database language of SQL, we actually use specially written functions. These functions are custom written by you and are known as criteria.create mysql dashboardReplyDelete
I never comment on blogs but your article is so best that I never stop myself to say something about it. You’re amazing Man, I like it Database Errors... Keep it upReplyDelete