differential is as same as full

  • sqlfriend

    SSC Guru

    Points: 52347

    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.

  • Sreekanth B

    SSCertifiable

    Points: 6139

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

    This is different.

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

     

     

  • Sreekanth B

    SSCertifiable

    Points: 6139

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

    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 1 week, 1 day ago by  sqlfriend.
  • Jeffrey Williams 3188

    SSC Guru

    Points: 88150

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • sqlfriend

    SSC Guru

    Points: 52347

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

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

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

    Thank you all

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

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