How to disable Data Compression on tables in whole database in SQL Server 2008R2?
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!