We are taking backups (Full, Differential, and Transaction Log) of databases using the maintenance plans feature in SQL Server.
While taking the backup, I have chosen the compress option to compress the backup files, and I have enabled "backup compression default" also.
Full backup maintenance plans are compressing the backup file; file size is 4 times less than the database size. But when we comes to differential backup, I think the compression setting is not working.
I have observed today's backup differential file. Yesterday 4 tables in a database got populated with 10GB of data each, so differential backup file size crossed 40GB. I am certain these were the only changes, because it is a fresh database, and there were no maintenance activities run on that day.
.ldf file size grew to 500 GB on the day the backup was performed. This might cause an issue for big size, but database size (
.mdf) is less than 50GB.
Please look at the below image for additional info:
I want to confirm that compression will not work for differential backups, or perhaps there is something else I missed?
If it does not work, based on Paul Randal's database percent change script, I may decide to take full or differential backup.
Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64) May 4 2015 18:48:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
select type, backup_size, compressed_backup_size from msdb..backupset where backup_start_date >= '20170927' and database_name = 'yourDB'
type backup_size compressed_backup_size I 213,473,351,680 42,147,293,093