Restoration failure due to edition mismatch & its feature limitations

Today, I had a side by side migration which is a Non-Prod Environment. Before doing it on production server, we used to do its corresponding non-prod server. In this type of upgrade we won’t do the SQL Server installation on a new server, we have other team to do the installation as per the customer requirement.

So happily started the event, I was taking backup through litespeed (a compression software which helps to take a compressed backup)from source and started doing the restoration on target server, all the database restoration went out well but on the last one, I got an Error!!! (actually happy to see it).

Error

Msg 62301, Level 16, State 1, Line 0

SQL Server has returned a failure message to LiteSpeed for SQL Server which has prevented the operation from succeeding.

The following message is not a LiteSpeed for SQL Server message. Please refer to SQL Server books online or Microsoft technical support for a solution:

Processed 467664 pages for database ‘Data1’, file 'File1' on file 1.

Processed 64336 pages for database ‘Data2’, file 'File2' on file 1.

Processed 616784 pages for database ‘Data3’, file 'File3' on file 1.

Processed 5 pages for database ‘Data4’, file File4' on file 1.

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.

The catch point on the error message is 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

Surprised to see because as per standard none of our application database will be having compression or vardecimal storage.  I know that I am working on Standard Edition where as my source is Enterprise Edition. We know that data compression is not available except Enterprise Edition, we can have more idea about the edition supported feature in this article.

So what next I have to restore the database as per the plan. So the plan is:

1. Find the compressed objects

2. Remove the compression

3. Take a backup and restore it on target server

Step-#1(Find the compressed objects)

Restore the same backup on a source server with some other name as DB2 [I cannot make any changes to the source database].

 Run the following “Object Finder” query to identify the objects which are having 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

Output

Step-#2 Remove the compression

We can remove the compression with the Index level or table level, In my output above I have both Index and Table level compression so I used both the following queries to uncompress it.

Index level (Syntax):

ALTER INDEX ALL ON <TABLE NAME>REBUILD WITH (DATA_COMPRESSION = None);

Index level (Example):

ALTER INDEX ALL ON Object1 REBUILD WITH (DATA_COMPRESSION = None);

Here is the script, which creates a script that removes the compression from the indexes on those tables listed in the Step-#1

SET NOCOUNT ON

SELECT DISTINCT 'ALTER INDEX '+ '[' + i.[name] + ']' + ' ON ' + '[' + s.[name] + ']' + '.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=NONE);'FROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK)ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas s WITH (NOLOCK)ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
join sys.partitions
ON sys.partitions.object_id = o.object_id
AND sys.partitions.object_id = i.object_id
AND i.index_id = sys.partitions.index_id
WHERE o.type = 'U' AND i.[index_id] >0
and data_compression in (1,2)

Table level (Syntax):

ALTER TABLE <TABLE NAME>REBUILD WITH (DATA_COMPRESSION = None);

Table level (Example):

ALTER TABLE Object1 REBUILD WITH (DATA_COMPRESSION = None);

Here is the script, which creates a script that removes the compression from the tables listed in the Step-#1

SET NOCOUNT ON

SELECT DISTINCT 'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=NONE);'FROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK)ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas AS s WITH (NOLOCK)ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
join sys.partitions
ON sys.partitions.object_id = o.object_id
AND sys.partitions.object_id = i.object_id
AND i.index_id = sys.partitions.index_id
WHERE o.[type] = 'U'and data_compression in (1,2)

Step-#3 Take a backup and restore it on target server

Cross check with “Object Finder” script is there any more compressed tables on the database, there were no rows retuned from the scripts. So Just taken a backup and did a restore on a target server.

Carried out all other subtask to complete the migration then sent an email to the customer stating that migration has completed successfully & sent a note about compression too.

Note:  Here is the script to find the objects which has vardecimal  data types

SELECT OBJECTPROPERTY(OBJECT_ID(‘<object name(s) from Object Finder output>’), 'TableHasVarDecimalStorageFormat') ;