1

I have a production system running on an SQL Server 2008 DBMS, in Full recovery mode.

I take a full backup each day at midnight, a log backup every two hours, and a differential backup every 6 hours (06:00, 12:00, 18:00, but not midnight as the full is taken then).

In recent days however I've noticed that the diff backup filesize of 18:00 is smaller than the one of 12:00. And sometimes, the one of 12:00 is smaller than the 06:00 ...

I did not experience this behavior until recent days.

Reading from the Microsoft doc, the filesize of a diff backup should always be larger than the previous one, until a new full backup is taken.

Could anyone have a possible explanation what could be causing this ?

Thanks.

tjeuten
  • 649
  • 9
  • 28
  • possible duplicate of [Microsoft Sql Server Managment studio backup size goes negative](http://stackoverflow.com/questions/3457600/microsoft-sql-server-managment-studio-backup-size-goes-negative) NB: @tjeuten If you don't think this applies please say so! – Martin Smith Sep 02 '10 at 15:00
  • @Martin: thanks for the link. But still not entirely clear to me. I always thought that in Full Recovery mode, full (and diff) backups were entirely separated from a log backup, but if I understand the other thread correctly, the log IS taken along with a full or diff backup ?? Correct ?? – tjeuten Sep 03 '10 at 08:09
  • Have you got the "INIT" option set for the differential backups? – MrEdmundo Sep 03 '10 at 15:59
  • Actually I'm using Red Gate's SQL Backup tool, so not sure if that sets the INIT option. But in the SQL Backup dashboard, the uncompressed diff backup size is shown as smaller than the previous one, so it's not caused by SQL Backup compressing it. – tjeuten Sep 04 '10 at 08:49

1 Answers1

2

I had a similar situation. I had a bit thought over it and reckoned it's caused by multiple backup jobs existing for the database, which means the differential backup was mislead by the wrong backup flag.

Kye Fang
  • 21
  • 2