Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Orion Platform > Failure to migrate database from SQL Server 2008 Enterprise to higher SQL version

Failure to migrate database from SQL Server 2008 Enterprise to higher SQL version

Updated October 24, 2017

Overview

While trying to migrate the database from SQL Server 2008 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)

Environment

  • SQL Server 2008 R2 Enterprise edition

Cause

We cannot restore a backup of a database that has compressed objects in SQL Server 2008 Standard edition because data compression is only supported in Enterprise edition. For more information, see: 
 
http://technet.microsoft.com/en-us/library/cc645993(SQL.100).aspx

(© 2017 Microsoft, available at https://technet.microsoft.com, obtained on October 24, 2017)

Resolution

On an Enterprise Edition, first identify and then remove the objects that have compression on the database.

 

  1. Run the script below on the database that is suspected of having compressed objects. This script gets 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
  2. To check for vardecimalstorage format compression, run the following command:
    SELECT OBJECTPROPERTY(OBJECT_ID(‘<object name(s) from above command output>’),
                'TableHasVarDecimalStorageFormat') ;
    GO
  3. 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);
  4. Re-run the script to determine if any compression is still there.
  5. Proceed with restoring the database on the newer SQL server. See:
    https://www.howtogeek.com/50354/restoring-a-sql-database-backup-using-sql-server-management-studio/
    (© 2006-2017 How-To Geek, available at https://www.howtogeek.com, obtained on October 24, 2017)
 

 

Last modified

Tags

Classifications

Public