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:
- Backup (1) SQL DB from where you want to take backup to restore 😉 (Used in case you messup anything in SQL)
- 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.