Hi,

Today I faced this problem when trying to restore a Dynamics CRM 2011 database from a SQL Server 2008 R2 Enterprise Edition to a SQL Server 2008 R2 Developer Edition.

It got me crazy as I did not created any partition on the original DB and this problem didn’t allow to me to restore the DB and import the organization on my dev environment.

After some research we came up with this KB article which explains the cause and solution for this issue. In a nutshell:

[Cause]

When Microsoft Dynamics CRM 2011 is installed using a Microsoft SQL Server Enterprise edition, a partition is automatically created for the auditing functionality of Dynamics CRM 2011. The AuditBase table then uses partitioning (which is only available for Microsoft SQL Server Enterprise).

[Solution]

1. Backup current DB, just in case :p).

2. Run the following script on the original DB:

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

3. Just in case, verify that your current CRM 2011 continue working properly.

4. Now, backup the DB again and copy the backup to the environment you want to restore it in order to import the organization.

5. Try restoring the DB again and then importing your organization through Deployment Manager as usual. It should work fine now :).

Hope it helps and saves you some time,

PP [twitter: @pabloperalta]