Solution: The file "MDF" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Posted by blakhani on May 22, 2014
Recently I was trying to create a database on my SQL Instance using below command
CREATE DATABASE [MyDatabase] ON ( NAME = N'MyDatabase', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase.mdf') LOG ON ( NAME = N'MyDatabase_log', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase_log.ldf') GO
But encountered below error
Msg 5118, Level 16, State 1, Line 1 The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Error giving hint about some kind of compression. So if we look at the folder which is specified E:\BlogContent\DatabaseFiles it was in blue color.
Blue color actually indicates that folder is compressed. Right Click on folder, go to “Properties” and click on “Advanced…”
The checkbox “Compress contents to save disk space” is checked which means compression is enabled. Once we uncheck the box, we should be able to create database.
Here is official stand about compression http://support.microsoft.com/kb/231347/en-us (SQL Server databases are not supported on compressed volumes). There is a great blog by Ken Henderson on this topic detailing the ramifications and I would encourage you to read it http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx
If we try to restore a database and keep files on compressed folder, same error is raised (in SQL 2014 Management Studio)
TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore of database 'MyDatabase' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup.
The file must be decompressed. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
Hope this helps!
Help: SQL Server 
manu said
Thanks for sharing.
Rob StGeorge said
Thanks for this, saved me a lot of time troubleshooting!
blakhani said
My pleasure Rob. Glad it could help you.
Luis said
Very useful, thank you very much
blakhani said
Thanks Luis
Sérgio Ferraz said
Very Good !!
Thanks for sharing !!
Je3f said
it works! thanks for sharing!
Rose Maria said
This is very interesting, You are a very skilled blogger. I have joined your feed and look forward to seeking more of your magnificent post.
Also, I’ve shared your website in my social networks!
lunarwolfs said
Thanks. It worked for me
blakhani said
Thanks!
גרי רשף said
Thanks: that was the problem when I tried to restore a database.
yosef said
how handle this by c#?
Sadia said
Thanks it saved my time
satar said
i don’t know how to express my special thank to You!! all i can say is thank you for your constructive guide!!!! 🙂
blakhani said
Thanks!
Faraz said
Thanks.
Chirag Mukherjee said
Awesome Solution. Saved so much time. Highly Appreciated
blakhani said
Thanks!
SHishir Jaiswal said
Thank you my friend, you saved a lot of my time.
ing said
Thx. This article can solving my problem.
Cebi said
This also helped me, thanks.
Ken T said
We were trying to restore a user DB today and were failing with this error. Unchecking that compression attribute on the folder worked for us. I originally thought the issue might have been that we were handed a compressed Avamar backup from our server team; but now I’m thinking a creation or restore ANY formatted DB would have failed until we took this action. Thanks.
blakhani said
Thanks for sharing this Ken.
Edward said
Well done.. I finally i can work fine
avanthi nagar said
Thanks for sharing worked for me.
leandro sardinha said
Thank you very much… saved my day
shahab said
thanks alot.got stuck in it for about 2 hours.thank you again
glutenfreesql said
Thanks, this worked!
Enis said
Great!
Thank you too. Saved my time.
JermsTheITGuy said
OMG Hours I spent searching for this. Thank You so Much!!!!!!
blakhani said
Thanks for sharing!
DonkeyKong said
Database ‘SSISDB’ does not exist. Make sure that the name is entered correctly. (Microsoft SQL Server, Error: 911
just want to leave this here so that anyone else with this problem can hopefully get pointed her sooner than I was!! THANK YOU!
Kuldip said
Thanks for sharing this.