Introduction
One of my customers installed a trial (enterprise) version of SQL and on that trial version he installed a Dynamics CRM 2015 On premises. When the trial version of SQL expired, the standard SQL version was bought and installed. After the installation of the standard version, the CRM databases went into “suspect” mode and could not be used anymore.
Source of the problem
It seems the trial version of SQL leaves behind a partition which is only supported by the enterprise edition of SQL. Other version do not support that partition and as a consequence set the database in suspect mode.
Solution
The solution for this problem can be found in this Microsoft KB: “Database cannot be started in this edition of SQL Server” error when restoring a Microsoft Dynamics CRM database“.Whereas this KB is for a Microsoft Dynamics CRM 2011 environment, it also works for an 2015 environment.
The general steps to solve the problem are:
1. Restore the ‘Org_MSCRM’ database to a Microsoft SQL Server Enterprise edition. It is recommended to backup and restore the database instead of running the script on the production database.
Notes:
- If you don’t have another Enterprise edition, you could install a new trial version and restore the database that way.
- If you can’t backup the database (as it is in suspect mode), you could just copy the files of the database and attach them on the other server
2. Run the following script against the restored database.
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
Note:
- You do not need to change anything to the script; no name changing is necessary
3. Once the script is complete you can backup the database and now you should be able to restore the database to a Microsoft SQL Server Standard edition.
Image source: How to recover a suspect msdb database in SQL Server