Error restoring a Microsoft Dynamics CRM database: Database cannot be started in this edition of SQL Server

NOTE: Backup the CRM database first

Steps:
1. Run the below script on the CRM Database that want to restore.
2. Created the backup of the database.
3. Restore the database to the 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Workflow Elements by Aiden Kaskela

Business Solutions (CRM) MVP, Contractor, Evangelist

MSDYNAMICSBLOG BY DEEPESH

Real Experiences from Analyzing, Architecting, developing and working with Dynamics CRM, Dynamics 365 and sometimes .net

Mark Margolis's Blog

It's about Software, CRM, Product Development and sometimes Food.

GT // CRM

Gareth Tucker on Microsoft Dynamics CRM

Donna Edwards

Sharing my Journey with Dynamics 365

The WordPress.com Blog

The latest news on WordPress.com and the WordPress community.

%d bloggers like this: