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

PowerShell TTUC #15 – File Name with Time Stamp

PoweShell TTUC (Tips, Tricks and Useful Commands) #16 – File Name with Time Stamp File can be created with date / time suffix using the following syntax / commands: New-item -type file -Name (“MyFile_$(Get-Date -f Read more…

Microsoft

F5 VPN Plug-in and NPuroamHost.dll Issue

By default, the F5 VPN plug-in (F5 Networks Firepass Host Plugin) doesn’t install from Internet Explorer 11 browser.  If you try the manual installation option, you will get only the NPuroamHost.dll file. Copying and pasting Read more…

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…