SQL SERVER – FIX – Error: 905, Severity: 21, State: 1 – Database ‘xxx’ cannot be started in this edition of SQL Server because it contains a partition function ‘xxx’.

Recently one of my blog reader contacted me via mail and told that after upgrading from Enterprise Evaluation Edition to Standard Edition, the database was not accessible. To troubleshoot, I have asked them to share SQL Server ERRORLOG. There are various ways to read Errorlog. Here is the method to read Errorlog:

• In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.
• Right-click a log and click View SQL Server Log.

Other ways to get Errorlog are explained in detail by Balmukund (b|t) on his blog (Help : Where is SQL Server ErrorLog?)

In the Errorlog, I asked him to look for any error related to the database which is not accessible and soon they shared below messages:

2015-02-20 13:47:36.65 spid7s      Error: 905, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started in this edition of SQL Server because it contains a partition function ‘myRangePF1’. Only Enterprise edition of SQL Server supports partitioning.
2015-02-20 13:47:36.65 spid7s      Error: 933, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Explanation

From the error we can see that the database is using partition function which is enterprise only feature. Here is the link which has edition comparison.  The database contains one or more partitioned tables or indexes. Standard edition of SQL Server cannot use partitioning. Therefore, the database cannot be started correctly. Partitioned tables and indexes are not available in every edition of Microsoft SQL Server. Books online topic “Features Supported by the Editions of SQL Server 2012” has complete list of feature.

Solution

I asked him to restore the database MDF and LDF files to another instance which is either developer, enterprise or enterprise evaluation edition. Then get rid of the features which are enterprise only feature. They can easily be found using the DMV sys.dm_db_persisted_sku_features

USE <DatabsaeName> -- Change the name to the database where we need to check
GO
SELECT *
FROM sys.dm_db_persisted_sku_features

Note that the query has to be run in the database which is under question, not the master database. Here are the possible values on SQL Server 2014.

• TransparentDataEncryption
• Compression
• ChangeCapture
• ColumnStoreIndex
• InMemoryOLTP
• Partitioning

Once the object referring to that feature is removed, the backup can be taken and restored on “lower” edition of SQL Server.

Here is another error which can be received while restoring database using compression

Msg 909, Level 21, State 1, Line 1
Database ‘SqlAuthority’ cannot be started in this edition of SQL Server because part or all of object ‘PageCompressionTest’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

I always advice to run the DMV and check the features whenever there is a downgrade of edition. Even though evaluation to standard is an upgrade but I would call it as downgrade because there are few feature of enterprise editions would not be available in standard edition.

Have you ever encountered any downgrade scenarios and got some errors?

Reference: Pinal Dave (https://blog.sqlauthority.com)