asked
Database restore error
I'm trying to restore a backup that came from a machine with SQL Server 2012, 64 bit - **Enterprise** Edition (no service packs) to a different machine with SQL Server 2012, 64 bit - **Standard** Edition (no service packs) The error that I'm getting is Database 'X' cannot be started in this edition of SQL Server because part or all of object 'x' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Database 'X' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909) I tried to then find all of the objects/tables that had datacompression enabled for that database, using the following query. select so.name,so.type,so.type_desc,sp.data_compression,sp.data_compression_desc from sys.partitions sp inner join sys.objects so on (so.object_id=sp.object_id) where sp.data_compression!=0 There were 8 different tables that came up, but when I went to check each one I found that the vardecimal storage format is enabled was set to false (again, for each). However, each table has compression type of Page. I dont know if that matters, but thought I would include that just in case. Is there a way to change the compression type? I can't using the gui. And would changing the compression type allow me to then restore the backup to the Standard edition server's database?
You need to remove compression from the tables completely to restore it to a Standard server This will detail how to remove compression
https://msdn.microsoft.com/en-us/library/hh710073.aspx But compression was enabled for a reason, you will increase the space of the database so ensure you have accounted for the extra space needed.
18 People are following this question.
