• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

How to disable Data Compression on tables in whole database in SQL Server 2008R2?

February 12, 2017

If you restore database from enterprise edition to Standard edition and you have enabled "Data compression" on table, restoration fails. Data compression is available only Enterprise edition in SQL Server 2008R2. The best way how to disable data compression is the following:


1) Make a clone of your database where data compression is enabled on enterprise editon.


2) Then run this script on clone database - ALTER TABLE.



3) Then run this script on clone database - ALTER INDEX.


And ALTER INDEX command in Result panel copy and execute in new query window.


Note: You must have enough space on disk where is your data file of database for decompress data!!


You can check if feature "Data Compression" is disable or another Enterprose feature with this command below:



4) Done! - Now you can backup clone database and restore on Standart edition.


That is all! See you soon!





Please reload

Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon