Downgrade Cb Protection Database from Enterprise to Standard edition
Cb Protection, all versions (7.x, 8.x)
This document outlines the steps necessary to remove Enterprise level features (data compression) from the 'das' database, in order to attach it to a SQL Standard Edition server.
To remove data compression from an existing 'das' database, it will be necessary to have SQL Enterprise Edition currently installed.
1) First, confirm that data compression is the only Enterprise feature enabled by running the following query on the database
SELECT * FROM sys.dm_db_persisted_sku_features;
The expected output is
2) Next, stop the Cb Protection Server and Cb Protection Reporter services.
3) Execute this SQL to generate a list of objects that have data compression enabled
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
4) For each object returned in #3, run the following statement to remove the compression
ALTER INDEX ALL ON objectNameFromStep3 REBUILD WITH (DATA_COMPRESSION = None);
5) After this is completed, run the statement from #1 again to confirm that no data compression exists. If it does, run through #3 and #4 again to take care of any objects that were missed.
6) Detach the database from the current SQL Enterprise Edition server, and move / attach is to your SQL Standard Edition server.
7) Restart Cb Protection Server and Cb Protection Reporter services.
What if I have already moved the database to SQL Standard Edition before removing data compression?
SQL Standard Edition will not allow you to attach a database that contains data compression. If this is the case, you will need to attach the database to an Enterprise edition server in order to make the changes. Often this can be done by creating a temporary VM, installing a trial version of SQL Enterprise Edition, and moving the database there to run through the above steps.
I'm getting the following error when attaching the database to SQL Standard Edition:
Database 'das' 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.
This error is due to the database still containing data compression, and this is not supported by SQL Standard Edition. You will need to run through the steps above, using SQL Enterprise Edition, to remove any data compression from the das database before it can be attached to SQL Server Standard Edition.