SQL Server databases are not supported on compressed volumes

Problem Description

This is related to the MS SQL server administration. There is a common mistake of compressing the drives instead of shrinking the files to gain more space in the server. It is important to understand that SQL Server database on compressed volumes are not supported. There are two scenarios one for SQL 2000 and other one is for SQL 2005.

SQL 2000
In SQL Server 2000, you could create a new database with its .mdf and .ldf files located on NTFS or FAT compressed drives. The interface doesn't restrict you from doing this

SQL 2005
As opposed to SQL 2000, SQL 2005 doesn't even allow you to place data and log files of Read/Write databases on compressed volumes.
For example, Let's create a database called Compressed_DB with .mdf and .ldf files located on a compressed file system (E drive in my case here). You will get an error: Create failed for Database 'Compressed_DB'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The file "E:\Compressed_DB.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5118) This is because a read/write databases primary .mdf and .ldf files cannot be created on a compressed volumes in SQL 2005. Also, if you try to restore a database backup (.bak) file of a read/write database with its .mdf or .ldf or both pointed on a compressed drive, you will again get an error as follows: Restore failed for Server ''. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: The file "e:\Db.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. (Microsoft.SqlServer.Smo)

Solution Description

Log files should never be placed on compressed file systems. System database (master, msdb, model and tempdm) cannot be placed on compressed drives. If you want to restore or take a backup please be sure that your drive is not compressed.