Cannot find reason why differential backups size reduced

Question

Sql Backup job summary:

Hi, I have a backup job that take full backup first day of the month and take differential backup every 2 hours between 9am and 5pm everyday.

Problem: While testing disaster recovery plan I found that after few differential backups size of differential backup reduced and when I include reduced sized differential backup(red square) I get this error: in restore

Any idea why size of differential backup reduced? and how I can restore this differential backup?

Please help and thanks in advance for reading and answering this question.

Update As mentioned job is taking full backup start of month there is no full backup between differential backup size of differential backup is increasing till position 91 However, as you see in image no full backup is taken before position 92.

Answer 1

The most likely cause by far is that a full backup was taken out of sequence (between that differential and the previous full backup) - that makes the differential smaller as there are less differences to include between it and this extra backup than between it and the expected previous backup.

If this is the case then to be able to restore the unexpectedly small differential you need to find a copy of the extra backup.

This is usually because someone has taken a copy for use elsewhere, perhaps for testing a support issue or new release. IT is important that everyone who has access to be able to take backups knows that they need to be careful of this because it breaks your DR plans and so forth.

There is a safe way to take an extra backup: use the COPY_ONLY option. This takes the backup as normal but does not interfere with your main full/diff/log backup sequence.

Answer 2

I wonder if you were to create a large index, do a Diff backup, delete that large index, do a Translog backup (which will be large), then your next Diff backup is smaller than the last one. I've seen that happen myself.

Answer 3

You say that no other full backup was performed.

But if the server is backed up using some backup software solutions, that can be seen by SQL Server as a backup.

We use a network backup software that includes an agent on the servers being backed up. When backups are configured, we can set rules to include SQL Server and/or Oracle databases as part of the backup set.

If we do that, SQL Server is aware of the backups and resets the differentials.

Is it possible your environment is doing something similar?

---

If you run this query (modified to exclude log backups):

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
  and msdb..backupset.type  <> 'L'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

You will get some data that can help confirm or deny this possibility. You want to watch these columns for clues:

1. Backup start dates that do not line up wit your backup job's schedules.
2. Physical Device Name column entries that aren't the path you use in your scheduled backup jobs.
3. Backupset Names that aren't what your backup jobs set.

On point 2, for example, I use the backup jobs from Ola Hallengren. In my environment, I use them to back up to a server share. So that column in the results looks something like this for the database "DB" (with Full or Diff depending on backup type):

\\FileServer\share\DBServer\DB\FULL\DBserver_DB_FULL_[date]_[time].bak

But in our environment, the CommVault backup software agent creates device names that are GUIDs like 1c20c18e-6f55-4bc5-86d3-5db7f9eca5d9

On point 3, the Ola H. scripts do not set a backupset name column. But the CommVault software generates this backupset name: CommVault Galaxy Backup.

As an aside, if you aren't using the Ola H. backup scripts, I highly recommend them.