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 – 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…

Microsoft

Verify Service Status Remotely Using Local Account – PowerShell Script

I have modified one of my previously published script – Stop, Start, Disable Service Remotely–PowerShell Script (http://portal.sivarajan.com/2011/05/stop-start-disable-service.html) to use Local account (instead of a domain account) to verify the status of the service.   Input Read more…

Microsoft

Collect Computer Information From Active Directory– PowerShell Script

This PowerShell script can be used to collect computer information from Active Directory.   I am searching only Windows XP and Windows 7 machines.  You can update these values  by modifying $OS1 and $OS2 variables. $OS1 Read more…