I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a backup/restore down edition will work. I found partitioning and compression which I removed but sys.dm_db_persisted_sku_features still tells me partitioning exists. After looking closer I found several partition schemes and functions with names like ifts_comp_fragment_data_space_xxxxxxxx and ifts_comp_fragment_partition_function_xxxxxxxx respectfully which are set to is_system=1. When trying to remove them I get the error “Cannot drop the partition scheme 'ifts_comp_fragment_data_space_ xxxxxxxx ', because it does not exist or you do not have permission.

Apparently these are automatically created when full text indexes become large. I’ve removed all full text catalogs but the schemes and functions remain. Thinking they may not matter I tried the backup /restore. Within the restore error message was “Database '' cannot be started in this edition of SQL Server because it contains a partition function 'ifts_comp_fragment_partition_function_xxxxxxxx".

I’ve only found one solution here which forces the objects is_system property to 0 but it comes with a disclaimer that the solution probably isn’t supported by Microsoft. Assuming this would disqualify us for support in the future this is not an option for me.

Is there any other way around this problem? A way to remove these partitioning schemes and functions?

  • 2
    It may be simplest to either (a) create a new database without full-text enabled, copy all of the existing data using import/export wizard, SSIS, generate scripts, etc., then back up the new database and try to restore that. Or (b) just create an empty database on the web edition instance and use the import/export wizard, SSIS, generate scripts, etc. to move the data to the new database. – Aaron Bertrand Nov 25 '15 at 1:08
  • That would work but the server has mirroring and transactional replication which make it no so simple to recreate it from scratch. – Dylan Nov 25 '15 at 1:39
  • And you expect mirroring and transactional replication to just keep working after restoring the database on another instance? – Aaron Bertrand Nov 25 '15 at 1:42
  • I'm planning to use the backup to initialize replication so i need the partitioning removed from the enterprise instance where it is. – Dylan Nov 25 '15 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.