Wednesday, October 2, 2013

"Database cannot be started in this edition of SQL Server" error when restoring a Microsoft Dynamics CRM database , MS CRM 2011

while restoring MS CRM 2011 db i got the error message as below
""Database cannot be started in this edition of SQL Server" error when restoring a Microsoft Dynamics CRM database , MS CRM 2011".
the below lonk says how to do...
http://support.microsoft.com/kb/2567984

this is cause of the SQL version....
hope this will definitely help :P

below is the script

what i suggest to do is, donot run this script directly in the Org_MSCRM DB.
just restore it as Temp DB and then run the script on Temp DB then take the backup from Temp DB and restore in your destination server.

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

Regards,
yes.Sudhanshu

No comments:

Post a Comment