Category: Database Backup

“AuditPFN” Issue while restoring MS Dynamics CRM 2011 Database Backup

While restoring CRM 2011 Database if you face following Error:

Error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database ‘DatabaseName’ 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 ‘DatabaseName” cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 905).

Cause:

The cause of above exception is the SQL Server you are importing from is SQL Server Enterprise Edition but SQL Server you are importing to is not same.

While creating Org in SQL Server Enterprise Edition a partition is created in the DB for auditing purpose. The AuditBase table uses partition which is available only in Enterprise Edition.

Resolution:

  1. Backup (1) SQL DB from where you want to take backup to restore 😉 (Used in case you messup anything in SQL)
  2. Run given script against that DB.

[sourcecode language=”sql”]

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

[/sourcecode]

3. Backup (2) DB again (Used for import on you non Enterprise Edition SQL Server)

4. Restore the above backup [Backup (2)].

Not My solution J Just found Online here.