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 > Network Performance Monitor (NPM) > Unable to restore database either due to folder permissions or compression

Unable to restore database either due to folder permissions or compression

Updated October 7, 2016

Overview

When restoring the database, the restore fails either due to a permissions issue or a table compression issue. This occurs mostly when migrating the SQL database to a new server. Database management is out of scope of Support. Always backup your database before performing any procedures.

 

Compressions Error:

Database 'SolarwindsOrion' cannot be started in this edition of SQL server because part or all of objects 'ContainerMemberSnapshots' is enabled with data compression or vardeciamal storage format. Data compression and vardeciamal storage format are only support on SQL server Enterprise Edition.

Database 'SolarWindsOrion' cannot be started because some of the database functionality is not available in the current edition of SQL server.

Environment

All NPM versions

Cause 

This occurs because of a permissions or table compressions issue.

Resolution

Permission issues

  1. Check which permissions the SQL server is using:
    1. Click Start > All Programs > Microsoft SQL Server 2016 > Configuration Tools > SQL Server 20xx Configurations Manager.
    2. Click SQL Server Services.
    3. Locate the service, typically SQL Server (MSSQLSERVER), being used for your database instance and note down the name as shown in  the Log On As column (typically NT Service\MSSQLSERVER).
  2. Locate the folder you want to restore to and add that user with full permissions to that folder.

 

Compression issues

  1. Run the following query against the Solarwinds DB in Microsoft SQL Server Studio Manager to find which tables have compression (Typically is will always be ContainerMemberSnapshots and LimitationSnapshots):
    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. Run the following query to find if any tables that have vardecimal storage format:
    SELECT OBJECTPROPERTY(OBJECT_ID(<object name(s) from above command output>),
                ‘TableHasVarDecimalStorageFormat’) ;

    GO
  3. Run the following query to alter the tables found in the previous queries
    ALTER INDEX ALL ON TABLE NAME
    REBUILD WITH (DATA_COMPRESSION = None);
  4. Back up your database and try restoring it again to the new server.

 

 

See how to identify compressed tables before restoring/migrating the database to any edition other than the Enterprise Edition of SQL Server 2008. To read more, click here.  (© 2013 Microsoft, available at https://msdn.microsoft.com/en-us/default.aspx,obtained on October 7, 2016.)

 

 

 

Last modified

Tags

Classifications

Public