Restoring a CRM 2011 database (SQL Enterprise to SQL Standard)
June 30, 2011
Posted by on
I came across this issue about a week ago when creating a replica vmware image for undertaking some testing in one of my projects. I was attempting to replicate a customer’s Live CRM environment on my image in order to do a bit of testing when i ran into an error i hadnt seen in a while!
“Database cannot be started in this edition of SQL Server”
A quick Google and i found this Microsoft Support article http://support.microsoft.com/kb/2567984. It turns out that if the Microsoft Dynamics CRM 2011 installer detects SQL Enterprise, it will automatically leverage its partitioning functionality. More specifically, the installer automatically creates a partition for the auditing data (AuditBase table). So if you’re using SQL enterprise, this is a good thing as the partition is beneficial from both a performance as well as management perspective. The auditing feature is still functional on SQL Server Standard edition, however there is no ability to delete an entire partition of the audit history.
So a couple of things to note…
You’ll notice that the error states that the database failed to start and not failed to restore. The restore cannot detect any mismatch in SQL versions so if you are restoring over the top of another database then this means that by the time you receive the error the database has already been restored and the database is unusable.
The Microsoft Knowledge Base article at the beginning of this post includes a script to run against the SQL Enterprise database to remove the partition prior to restoring to the database environment with SQL Server Standard Edition.
Remember, always back up the databases before making any changes.