Differential backups getting smaller.

Question

Differential backups getting smaller.

Balance

SSCrazy

Points: 2636
More actions
October 22, 2012 at 11:25 am

#263604

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.

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

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

Balance SSCrazy Points: 2636 More actions · Answer 1

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 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Balance SSCrazy Points: 2636 More actions · Answer 3

...an undoubtedly I caused it which is a bit embarrassing.

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 4

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)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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

Balance SSCrazy Points: 2636 More actions · Answer 6

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.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 7

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' );