differential is as same as full

Question

differential is as same as full

sqlfriend

SSC Guru

Points: 52769
More actions
November 8, 2019 at 5:37 pm

#3696330

I have a development server. We have backup setup on the server.
I noticed the differential backup 5.0 GB is almost the same size as the full backup. 5.1GB
I know there is little update on the database. Why the differential backup is so big?
We do full backup at weekend, and nightly differential.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 1

you had same question almost 8-9 years ago and the discussion had all the answers you need why a diff could become as big as a full backup or even larger than a full backup.

https://www.sqlservercentral.com/forums/topic/differential-backup-is-larger-than-full-backup

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

This is different.

The database in this case has little update daily, in fact nobody is using it.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 3

If little to no updates happening on the database, Any chance the Diff backups are getting appended instead of creating separate files?

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

I am using maintenance plan to do full and differential backup.

And I see there is a differential backup file every week day.

This reply was modified 4 years, 5 months ago by sqlfriend.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

Are you rebuilding your indexes every night - for example, through a maintenance plan? If so - then every page in the database is being updated and therefore needs to be included in the differential.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

No, I only reindex at weekend. And I don't see other jobs there.

I will change it to compressed backup to see if it makes a difference

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 7

Any specific pattern on what day of the week diff is starting to get bigger? Did you look for any other maintenance jobs you might have running against this DB on that day.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

you can do some research on the backups using the tables in MSDB database with a query such as:

SELECT bs.backup_set_id, bs.database_name, CASE bs.type WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'Filegroup' WHEN 'G' THEN 'Diff Filegroup' ELSE bs.type END AS backup_type,
    bs.is_copy_only AS is_copy, bs.backup_start_date, bs.backup_finish_date, DateDiff(minute, bs.backup_start_date, bs.backup_finish_date) AS backup_min,
    bs.name, bs.description, mf.physical_device_name, bs.user_name, bs.backup_size, bs.compressed_backup_size, bs.first_lsn, bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn
  FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily mf ON bs.media_set_id = mf.media_set_id
  WHERE bs.database_name = 'mydatabasename'
  ORDER BY bs.backup_set_id

This will show you the LSN range of each backup among other things.

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

sqlfriend

SSC Guru

Points: 52769

November 8, 2019 at 9:46 pm

#3696400

Thank you all