I followed my own article here for upgrading from SQL Evaluation to SQL Standard.  I had my Dynamics 2013 environment already stood up and unexpectedly already used for development work.

After the ‘upgrade’ steps, the database appeared to be in (suspect) mode and I didn’t have a backup (but a checkpoint via hyperv at least).

Anyway, I uninstalled the SQL Standard from programs and features.  Reinstalled the Evaluation version (which is Enterprise by the way) and ran this script on Microsoft’s support site against the CRM databases.

I then followed my article and the databases successfully stayed in normal operating non-suspect mode.

http://support.microsoft.com/kb/2567984/en-gb

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Microsoft

Azure AD and Manual UPN Update

In Azure AD, the UserPrincipalName (UPN) can be manually updated using Set-MsolUserPrincipalName Power Shell cmdlet.  The details and syntax are explained here – https://msdn.microsoft.com/en-us/library/azure/dn194135.aspx One of the common issues you experience during this process is Read more…

Microsoft

Azure Password Reset – The Password you’ve selected does not meet your Active Directory password policy

This is a common error message when you try to reset a password from Azure management port or Self service portal.  The error message is very clear here – “The Password you’ve selected does not Read more…

Microsoft

Azure – Your account is temporarily locked to prevent unauthorized use

Here is the another common error message when dealing with directory and password synchronization.  Error Message: Your account is temporarily locked to prevent unauthorized use. Try again later. Contact Customer Support if the problem persists Read more…