Downgrade SQL from Enterprise to Standard “Database cannot be started in this edition of SQL server” : Restore failed for Server Error in CRM 2011

Dear All

If you want to downgrade your database from the enterprise to standard here is the following solution



Reference URL:

https://ashwaniashwin.wordpress.com/2013/02/28/database-cannot-be-started-in-this-edition-of-sql-server-restore-failed-for-server-error-in-crm-2011/



Hello Everyone,

Today while trying to restore the Org_MSCRM Database from production server to one of my testing server, I came across a new Error:“Database ‘Org_MSCRM’ cannot be started in this edition of SQL Server because it contains a partition function ‘AuditPFN’. Only Enterprise edition of SQL Server supports partitioning. Database ‘Org_MSCRM’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error:

905)”

The error was very clear indeed as my production server was SQL Server Enterprise Edition and testing server was SQL Server Standard Edition.
Basically while installing CRM 2011 in Enterprise edition, a partition is created for auditing functionality in AuditBase table. And since this functionality is missing in rest of the SQL Server editions, the restoration of the Database from Enterprise Edition to Standard Edition throws error. The only possible solutions are either to remove the Audit partitioning or install the Enterprise Edition.

I then came across a Fast Publish article from Microsoft here describing about the issue in detail along with the Script to delete the partition.

Here is a script that you want to run by selecting your database 

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name='AuditPScheme')
BEGIN
 SELECT 
  CASE WHEN ind.type != 1 
   THEN 
    'DROP INDEX [dbo].[AuditBase].' + QUOTENAME(ind.name) + ' ' 
   ELSE ' ' 
  END + 
  'CREATE ' + CASE is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END  +
  ind.type_desc + ' INDEX ' + QUOTENAME(ind.name  COLLATE SQL_Latin1_General_CP1_CI_AS )  + ' ON [dbo].' +  QUOTENAME(OBJECT_NAME(object_id)) + ' (' +
   
  REVERSE(SUBSTRING(REVERSE((
   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
   FROM
    sys.index_columns sc
    JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
   WHERE
    OBJECT_NAME(sc.object_id) = 'AuditBase' AND
    sc.object_id = ind.object_id AND
    sc.index_id = ind.index_id
   ORDER BY index_column_id ASC
   FOR XML PATH('')
        )), 2, 8000)) + ')' +
  CASE WHEN ind.type = 1 
   THEN 
    ' WITH (DROP_EXISTING = ON) ON [PRIMARY]' 
   ELSE 
    ' ' 
  END  as Script 
 INTO #indexesScript
 FROM sys.indexes ind
 JOIN sys.partition_schemes ps on ind.data_space_id=ps.data_space_id 
 WHERE
  OBJECT_NAME(object_id) = 'AuditBase'
  AND ps.name = 'AuditPScheme' 
  AND is_unique_constraint = 0 
 SELECT * FROM #indexesScript 
 
 DECLARE @recreateScript nvarchar(max)
 DECLARE indScript CURSOR FOR
 SELECT Script FROM #indexesScript
 OPEN indScript
 FETCH NEXT FROM indScript INTO @recreateScript
 
 WHILE @@FETCH_STATUS = 0   
 BEGIN   
  BEGIN TRANSACTION t1
  Execute sp_executesql @recreateScript
  
  IF @@ERROR > 0
  BEGIN 
   ROLLBACK TRAN t1
   declare @message varchar(max)
   set @message = 'Audit history recreate index failed. SQL: ' + @recreateScript 
      RAISERROR (@message, 10,1)
  END
  ELSE
  BEGIN
   COMMIT TRAN
  END
  FETCH NEXT FROM indScript INTO @recreateScript   
 END   
 DROP PARTITION SCHEME AuditPScheme
 DROP PARTITION FUNCTION AuditPFN
  
 CLOSE indScript   
 DEALLOCATE indScript 
 DROP TABLE #indexesScript
END

After successfully running the Script, try to restore the DB again.

Note: Please take a backup before running the script on the Org_MSCRM database.

Even though the Auditing feature is still functional on Standard Edition, the ability to delete the partition of Audit History will not be available.

Thanks.