Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Differential backups getting smaller. Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 11:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:53 AM
Points: 484, Visits: 831
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.
Post #1375630
Posted Monday, October 22, 2012 11:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:53 AM
Points: 484, Visits: 831
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
Post #1375634
Posted Monday, October 22, 2012 12:09 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 40,197, Visits: 36,600
Now that's gotta be entertaining to fix.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1375658
Posted Monday, October 22, 2012 12:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:53 AM
Points: 484, Visits: 831
...an undoubtedly I caused it which is a bit embarrassing.
Post #1375662
Posted Monday, October 22, 2012 4:21 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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.




Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1375777
Posted Tuesday, October 23, 2012 5:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1375985
Posted Wednesday, October 24, 2012 2:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:53 AM
Points: 484, Visits: 831
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.
Post #1376360
Posted Wednesday, October 24, 2012 1:32 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:24 AM
Points: 258, Visits: 1,099
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' );

Post #1376644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse