Differential backups getting smaller.

  • I'm wondering if anyone can give some advice please - I'm restoring a live backup from Thursday night and will be restoring a differential from tonight later on but I've just noticed that the differential backup from last night is smaller than that on Friday & Saturday night - I cannot see that anyone has run a subsequent full backup so I'm wondering under what circumstances a differential backup could shrink?

    Many thanks.

  • What an idiot - I was getting the date's on files mixed up - looks like backup each night is writing to the wrong file name (so Saturday wrote to Tuesday). How we got there I don't know but panic over 🙂

  • Now that's gotta be entertaining to fix. 🙂

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ...an undoubtedly I caused it which is a bit embarrassing.

  • Well, at least you're cleaning up your own mess and learning from it. 🙂

    Trust me, we've all had to do that a few times along the way.


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • Balance (10/22/2012)


    What an idiot - I was getting the date's on files mixed up - looks like backup each night is writing to the wrong file name (so Saturday wrote to Tuesday). How we got there I don't know but panic over 🙂

    how this could be happened ? i thnk u would be adding datettime as SUFFIX to backup file for reference (through job or maintenance plan)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • No adding further naming to filenames makes automated actions much harder - it was pure and simple a mistake that shouldn't have happened, sorted now.

  • For what it's worth, you're welcome to use appropriate variations on the code below for your database backups. The backup file names contain timestamps:

    EXECUTE master.dbo.xp_create_subdir N'Z:\Database Backups\Differential Backups\model'

    DECLARE @DateString VARCHAR(16);

    SET @DateString = CONVERT(VARCHAR(16),GETDATE(),126);

    SET @DateString= REPLACE(@DateString,'-','');

    SET @DateString= REPLACE(@DateString,':','');

    SET @DateString= REPLACE(@DateString,'T','');

    EXEC( 'BACKUP DATABASE [model] TO DISK = N''Z:\Database Backups\Differential Backups\model\model_backup_' + @DateString + '.dif'' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N''model_backup_' + @DateString + ''', SKIP, REWIND, NOUNLOAD, STATS = 10, CHECKSUM' );

    DECLARE @backupSetId AS INT

    SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'model' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name=N'model' )

    IF @backupSetId IS NULL BEGIN RAISERROR(N'Verify failed. Backup information for database ''model'' not found.', 16, 1) END

    EXEC( 'RESTORE VERIFYONLY FROM DISK = N''Z:\Database Backups\Differential Backups\model\model_backup_' + @DateString + '.dif'' WITH FILE = ' + @backupSetId + ', NOUNLOAD, NOREWIND, CHECKSUM' );

Viewing 8 posts - 1 through 7 (of 7 total)

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