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') ;
