How to Repair ‘Compressed SQL Server Database’

SQL Server Data compression feature introduced in SQL Server 2008 & used to reduce the database size and improve the performance of I/O intensive workloads. SQL Server supports two types of compression: page & row compression which you can easily applied on SQL Server table, indexes etc. 

If your database get corrupted & the database has compressed tables or indexes then you cannot be restored & attached that database. If you try to repair it then it shows following error message:

Restore failed for Server ‘SQL_SERVER’.(Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Database 'DB1’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

If you want to repair compressed database then you first need to identify the compression and then remove the compressed objects.   

First you should determine that a database is using compression or not by using query the dynamic management view (DMV)sys.dm_db_persisted_sku_features. To determine whether the row is compressed or page, query the data_compression_desc column in the catalog view sys.partitions. 

After that Remove the compression by using following Query:

ALTER INDEX ALL ON <TABLE NAME>

REBUILD WITH (DATA_COMPRESSION = None);

After that you can use DBCC CHECKDB command to restore SQL Server database. The above steps are easy if your database has no severe corruption. In some corruption cases, you are unable to open your database & also unable to remove database compression. In this case, you have another solution to repair compressed database (Row & Page Compression) that is advanced SQL Server Recovery Software. Some advanced software has unique feature to repair SQL Server compressed database either the row compression or page compression. 

You should remember that Compression feature is not available for system tables.