When moving a database from a development environment to a production Environment, We backed up the database and restored it on the production database server which is a Sql Server Standard Edition.
I Got an error when restoring CRM 2013 Database Backup saying the AuditPFN is used in the database and it needs to be removed when using a standard edition server.
The AuditPFN means that a table can be spanned over multiple partitions, something that the Enterprise edition can do and the standard edition can not.
I hope there will not be any more surprises like this. I must say I was surprised to see the speed of moving the backup file which was some 10's of GB over the network, so doing multiple attempts was not a scary scenario.
the Error Message :
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore of database 'AAAAA_MSCRM' failed. (Microsoft.SqlServer.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.
------------------------------
Database 'AAAAA_MSCRM' cannot be started in this edition of SQL Server because it contains a partition function 'AuditPFN'. Only Enterprise edition of SQL Server supports partitioning.
Database 'AAAAA_MSCRM' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 905....
OK, I search the internet and found thess posts:
KB by microsoft with a quick fix : Article ID: 2567984 : "Database cannot be started in this edition of SQL Server" error when restoring a Microsoft Dynamics CRM database
XRM blog wrote this blog using MS KB : MS CRM 2011 Unable to restore SQL Server enterprise database to SQL Server 2012
The MS KB is a global solution but ALTAI just checked the database and found that the AuditPFN is used in just one table so they checked the installation of CRM on a Standard Edition server. They built the script for that table and view, dropped them on the standard server and re created the table on the enterprise edition. After that, their processed continued.
I am working on a CRM 2013 version, so I will try the ALTAI route first. I will have a CRM database in standard edition to compare the databases.
What I did:
I backed up the database and restored to a new Database.
I started with the check written by ALTAI to find the tables with partitioning option. It was just AuditBase.
Then I reverse engineered all the indexes and constrains, the table and the view, and then removed the AuditPFN options.
Afterwards I re-inserted all the records from the original database after checking there was no change.
The scripts I built is this:
GO
/****** Object: Table [dbo].[AuditBase] Script Date: 09/30/2014 03:48:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AuditBase](
[AttributeMask] [nvarchar](max) NULL,
[TransactionId] [uniqueidentifier] NOT NULL,
[Action] [int] NULL,
[ObjectId] [uniqueidentifier] NOT NULL,
[ObjectIdName] [nvarchar](1) NULL,
[UserId] [uniqueidentifier] NOT NULL,
[ChangeData] [nvarchar](max) NULL,
[CreatedOn] [datetime] NOT NULL,
[Operation] [int] NOT NULL,
[AuditId] [uniqueidentifier] NOT NULL,
[CallingUserId] [uniqueidentifier] NULL,
[ObjectTypeCode] [int] NULL
)
GO
ALTER TABLE [dbo].[AuditBase] ADD DEFAULT (newsequentialid()) FOR [AuditId]
GO
GO
/****** Object: Index [cndx_PrimaryKey_Audit] Script Date: 09/30/2014 03:49:12 ******/
CREATE UNIQUE CLUSTERED INDEX [cndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [fndx_ObjectTypeCode] Script Date: 09/30/2014 03:51:23 ******/
CREATE NONCLUSTERED INDEX [fndx_ObjectTypeCode] ON [dbo].[AuditBase]
(
[ObjectTypeCode] ASC
)
WHERE ([ObjectTypeCode] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [ndx_ObjectId] Script Date: 09/30/2014 03:51:40 ******/
CREATE NONCLUSTERED INDEX [ndx_ObjectId] ON [dbo].[AuditBase]
(
[ObjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [ndx_PrimaryKey_Audit] Script Date: 09/30/2014 03:51:54 ******/
CREATE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
(
[AuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [ndx_PrimaryKey_Audit_Primary] Script Date: 09/30/2014 03:52:19 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [ndx_UserId] Script Date: 09/30/2014 03:52:33 ******/
CREATE NONCLUSTERED INDEX [ndx_UserId] ON [dbo].[AuditBase]
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
GO
--
-- base view for Audit
--
create view [dbo].[Audit]
(
-- logical attributes
[UserIdName],
[CallingUserIdName],
-- physical attributes
[AttributeMask],
[TransactionId],
[Action],
[ObjectId],
[UserId],
[ChangeData],
[CreatedOn],
[Operation],
[AuditId],
[CallingUserId],
[ObjectTypeCode],
[ObjectIdName]
) with view_metadata as
select
-- logical attributes
[lk_audit_userid].[FullName],
[lk_audit_callinguserid].[FullName],
-- physical attribute
[AuditBase].[AttributeMask],
[AuditBase].[TransactionId],
[AuditBase].[Action],
[AuditBase].[ObjectId],
[AuditBase].[UserId],
[AuditBase].[ChangeData],
[AuditBase].[CreatedOn],
[AuditBase].[Operation],
[AuditBase].[AuditId],
[AuditBase].[CallingUserId],
[AuditBase].[ObjectTypeCode],
[AuditBase].[ObjectIdName]
from [AuditBase]
left join [SystemUserBase] [lk_audit_callinguserid] with(nolock) on ([AuditBase].[CallingUserId] = [lk_audit_callinguserid].[SystemUserId])
left join [SystemUserBase] [lk_audit_userid] with(nolock) on ([AuditBase].[UserId] = [lk_audit_userid].[SystemUserId])
GO
GO
/****** Object: PartitionScheme [AuditPScheme] Script Date: 02/17/2011 12:59:16 ******/
DROP PARTITION SCHEME [AuditPScheme]
GO
DROP PARTITION FUNCTION AuditPFN;
GO
