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:enter image description here in restore enter image description here

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.

  • 1
    Are you sure there is no full in between? check the full msdb.dbo.backupset – Stijn Wynants Aug 2 '17 at 9:20
  • A differential backup backs up the Extents where any changes have been made since the last Full backup. I assume there is a full backup before this smaller Differential and that's what is reducing its size. – Ramakant Dadhichi Aug 2 '17 at 9:24
  • Use below query after replacing the Where clause with your database name and you will get all backups done for that with time frame Select user_name, backup_start_date, type, (backup_size/(1024*1024)) as backup_size_in_MB, database_name, server_name, m.physical_device_name From msdb..backupset b Join msdb..backupmediafamily m on (b.media_set_id = m.media_set_id) where b.database_name = '' order by backup_start_date desc – Ramakant Dadhichi Aug 2 '17 at 9:26
  • Stijin kindly see my updates in question section I added new image and my question clearly explain that I take full backup at the start of month so no full backup in between. – shaadi Aug 2 '17 at 11:23

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.

|improve this answer|||||
  • "This is usually because someone has taken a copy" Are you taking about manual backups e.g database>tasks>Backup or automatic backup by running the jobs>job>start job? – shaadi Aug 2 '17 at 11:43
  • Note that FULL backup never breaks the log chain. – David Browne - Microsoft Aug 2 '17 at 13:01
  • David Spillett I am really confuse here please explain further - with respect to your comment: "This is usually because someone has taken a copy for use elsewhere............it breaks your DR plans and so forth". Are you referring to manual backup (rightclick>database>tasks>Backup) or starting backup job manually (SQL agent>Jobs>backupjob>start job)? – shaadi Aug 3 '17 at 1:12
  • Any method of taking a full backup would have the same effect. The UI routes in SSMS, agent jobs, manual "backup database" statements, etc. – David Spillett Aug 3 '17 at 7:12

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):

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.name AS backupset_name, 
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'

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):


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.

|improve this answer|||||
  • Can you share documentation / online resource on that so that I can share it with my system admin and take it to IT meeting because if this is the case then it is very alarming !! – shaadi Aug 3 '17 at 1:24
  • Not directly. I'm about to edit my post to include some indirect things that can help. – CaM Aug 3 '17 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.