Move SharePoint 2010 from SQL Server Enterprise (Trial/Evaluation) to Standard

Recently we had an emergency call from one if our clients who was migrating their databases from one Microsoft SQL Server 2008 to another Microsoft SQL Server 2008. No big deal, you would think, but unfortunately there is a caveat when using the SQL Server 2008 Enterprise or Trial edition. When restoring the Search Service Property Store Database the client ran into the following error:

Database ‘SharePoint_SearchServiceApplication_PropertyStoreDB’ cannot be started in this edition of SQL Server because part or all of object ‘MSSDocProps’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

The SharePoint 2010 Search databases make use of the feature set of the Enterprise edition when it is available.

In our case the client used the Trial edition of SQL Server which is actually the Enterprise version.

To remove the data compression from the database we need to run several SQL commands on the Search Property Store DB on the original database server.

Step 1, Find objects with issues

First we need to find the objects where the data compression is used

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
Step 2, rebuild the table index with no compression

For every line return in step 1 where object name is distinctive execute the following statement.

ALTER TABLE ON MSSDocProps REBUILD WITH (DATA_COMPRESSION = NONE);

Step 3 Run step 1 again

Execute the query from step 1 again. You will see that there are still objects with data compression, this are the indexes on the tables.

Step 4 Find indexes with issues

Use this statement to find the index name by IndexID from step 3

DECLARE @TableID int
SET @TableID = OBJECT_ID('MSSDocProps')
SELECT name
FROM sysindexes
WHERE
id = @TableID
AND IndID = 3 ‘ index number

Step 5, remove compression from index
To rebuild the index without compression use this command.

ALTER INDEX IX_Str ON MSSDocProps REBUILD WITH (DATA_COMPRESSION = NONE);

Now, you can do a detach/atach or backup/restore without any problems. Good luck!