Home > Development & Technical, Dynamics CRM, Microsoft, SQL Server > Microsoft Dynamics CRM 2011 – Database cannot be started in this edition of SQL Server

Microsoft Dynamics CRM 2011 – Database cannot be started in this edition of SQL Server

This article outlines what is required to repair the AuditPFN or Database cannot be started in this edition of SQL Server’ errors respectively.

If you try and restore a CRM 2011 database organisation from SQL Server 2008 R2 Enterprise Edition instance to a SQL Server 2008 R2 Standard Edition, then you will most likely hit the error shown below.

AuditPFNError

What does this mean?

When CRM 2011 is installed using the SQL Server Enterprise Edition, then a partition is created for the auditing functionality built in CRM 2011. The AuditBase table within CRM 2011 uses partitioning which is only available within SQL Server Enterprise Edition. If you then try and restore a backup of the database from SQL Server Enterprise Edition and restore it within SQL Server Standard Edition , then it will not start because SQL Server Standard Edition doesn’t support partitioning.

The fix/change Required

What you will need to do is use a T-SQL script to remove the partitioning from the backup SQL database. Follow the steps below to fix this issue.

1. Restore the ‘<yourcrmorgname>_MSCRM’ database to a server running Microsoft SQL Server Enterprise Edition.

2. Run the following script against the restored database. This script recreates all the indexes on the primary partition and then drops the partition.

Note: Don’t use your production or live database! Use a test server for this.

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 ‘ ELSEEND  +
 
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

 

SQLServerExecuteOutput

3. Once you have run the script (as shown above), you can backup the database.

4. Restore the database from step 3 to a server running Microsoft SQL Server Standard edition. This database should now restore with no errors (AuditPFN issue is gone) and start correctly.

Hope this helps?

Greg Olsen
Yellow Duck Guy

Advertisements
  1. December 18, 2011 at 3:09 am

    Hi

    Nice, post here may come back soon

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: