Version
Cb Protection, all versions (7.x, 8.x)
Topic
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.
Steps
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
USE das
GO
SELECT * FROM sys.dm_db_persisted_sku_features;
The expected output is
| feature_name | feature_id |
|---|
| compression | 100 |
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
SELECT
SCHEMA_NAME(sys.objects.schema_id) AS SchemaName
,OBJECT_NAME(sys.objects.object_id) AS ObjectName
,[rows]
,[data_compression_desc]
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
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.
FAQ
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.
External Reference
The SQL Post: Microsoft SQL Server: Error: 909 while migrating a Database from SQL Server Enterprise...
