Partial Contained Databases
I have a peculiar issue. I have some partial contained DBs that are being used by a CMS system and whenever I go to take a backup of these DBs they do not compress. I do have the server property database setting for Compress Backup checked. Has anyone else seen this before? I am not able to find much online about this unfortunately.
SQL Server Version: Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - 14.0.3391.2 (X64)
First step is to determine whether SQL Server tried to compress the backup, but the database contains data that doesn't compress much. (Have you ever try to zip a jpeg file?) You can investigate this using below:
RESTORE HEADERONLY FROM DISK = 'R:\Olf.bak'
Replace the file name with yours.
Check out the "Compressed" column.
If it is 1, then SQL server did compress it, but the data didn't compress (much). I believe that the compression of data that TDE result in, for instance, reduced the compression rate. Or perhaps you have lots of BLOBs in the database.
If it is 0, then you need to investigate why Ola's stored procedure didn't tell SQL Server to compress. It might be that you are using TDE, and you have an older version of Ola's script, and he has smarts in there to not try to compress TDE enabled databases (as I recall, on older version of SQL Server compression and TDE were mutually exclusive).