Recently one of my friends(Rookie DBA, Just started his career as a Jr DBA) called me and mentioned about this error he encountered while he was trying to restore a database(2008) from prod to Non-Prod Environment and was wondering how to make it work.
“RESTORE DATABASE is terminating abnormally.Msg 905, Level 21, State 1, Line 1.
Database ‘TstDB’ cannot be started in this edition of SQL Server because it contains a partition function ‘TstPF1’. Only Enterprise edition of SQL Server supports partitioning.”
Well, as you can see, the message thrown by SQL Server is pretty much self explanatory. The database is using Partioning which is Enterprise only Feature and hence you can’t restore this database to standard edition or Express edition. The solution is to drop all the partition schemes and partition functions prior to taking a backup and use that backup to restore on any Non-Enterprise edition(Except Developer Edition) instance! That’s the reason you should be very careful if you are using enterprise only features in any of your production databases. For example TDE is an Enterprise only feature, CDC is an enterprise only feature. Having any of these features turned ON on your database will make the DB restore process to Non-Enterprise editions(Except Developer Edition) little painful.
So, Is there anyway to retrive all the Enterprise Only features enabled on any given database? Yes, you can query the DMV “sys.dm_db_persisted_sku_features”
Query:
SELECT feature_name FROM sys.dm_db_persisted_sku_features;
GO
For example, I’ve a test database which has Data Compression and CDC enabled and the result is as shown below…
Btw, For looping through all the databases to identify enterprise only features enabled, you can write a simple loop or simply make use of “sp_MSforeachdb”.
Cheers! Happy weekend 🙂
MSSQLTREK 