Bagikan melalui

HOW TO IDENTIFY COMPRESSED TABLES BEFORE RESTORING/MIGRATING DATABASE to any edition other than Enterprise Edition of SQL Server 2008

  • Artikel

 PROBLEM DESCRIPTION

 ===================

While trying to migrate the database from sql2008 enterprise to standard you get the following error:

TITLE: Microsoft SQL Server Management Studio

------------------------------

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)

CAUSE

=======

We cannot restore a back up of a database which has compressed objects in sql2008 Standard edition because data compression is only supported in Enterprise edition.

https://technet.microsoft.com/en-us/library/cc645993(SQL.100).aspx

RESOLUTION

===========

àOn an Enterprise Edition, we first need to identify and then remove the objects which have compression on the database.

à Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data compression:

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

àTo check for vardecimalstorage format compression run the following command:

SELECT OBJECTPROPERTY(OBJECT_ID(‘<object name(s) from above command output>’),

  'TableHasVarDecimalStorageFormat') ;

GO

à For each object that is reported by the above select query you need to disable the compression by using the following command

ALTER INDEX ALL ON <TABLE NAME>

REBUILD WITH (DATA_COMPRESSION = None);

 àRe-run the script to check if any compression is still there

à If not back up the database in SQL2008 Enterprise and now the restore will succeed on a lower edition.

Additional Information

===================

SKU persisted features would not work across SQL Editions unless the edition supports it.

Refer: sys.dm_db_persisted_sku_features

The above article has more details regarded SKU persisted features.

Sakshi Jain

Support Engineer, Microsoft SQL Server PSS

Reviewed by
Sudarshan Narasimhan, Shamik Ghosh
TL, Microsoft SQL Server PSS

Comments

  • Anonymous
    March 16, 2015
    The comment has been removed

  • Anonymous
    June 15, 2015
    Thanks for this great info. I have about 50.000 objects in database and it's hard to find the object with SELECT OBJECTPROPERTY one by one. Is there another way to solve this issue? Thanks again.

  • Anonymous
    September 02, 2015
    if you want to convert a bigger DB you have to use a loop like this: 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] into #compr 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' while exists(select 1 from #compr) begin       declare @i varchar(200) = (select top 1 [ObjectName] from #compr)       print @i       exec ('ALTER INDEX ALL ON '+@i + ' REBUILD WITH (DATA_COMPRESSION = None)')       delete from #compr where [ObjectName] = @i end drop table #compr

  • Anonymous
    May 29, 2016
    Thanks for the article. I ran the scripts outlined but still had tables left using PAGE compression. I had to use the following command to sort those out:ALTER TABLE {TABLE} REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)

  • Anonymous
    December 25, 2016
    thanks.