January 14, 2011 at 8:29 am
I backup many production servers using full and differential backups. Usually a full on the weekend and differential every night.
One server, the most important server of course, after a full backup of 165GB on Saturday I get a differential backup of 2MB on Sunday. Monday's Diff BK is 10GB, Tuesday's Diff BK is 97MB, Wednesday's Diff BK is 140MB, Thursday's Diff BK is 6.5GB, etc.
Every other server after a full backup let's say the size is 107GB on Saturday. Sunday's Diff BK is 341MB, Monday's Diff BK is 4GB, Tuesday's Diff BK is 7GB, Wednesday's Diff BK is 10GB, etc. The size grows with every differential backup which is the way it should be.
I don't understand why the one server's backups are changing sizes from high to low during the week. I only have the Full BK on Saturday, Diff's every night, and Transaction Log BK's every hour. This seemed to start when I created a Copy_Only BK for replication purposes but I have since deleted the job so it's no longer available. Anybody?
January 14, 2011 at 8:34 am
Check out the backup history or sql logs to see if there's really nothing and noone rebacking up the db. Nothing else would make sense to me atm.
January 14, 2011 at 8:49 am
If I run: (backup history)
DECLARE @DBNAME VARCHAR(100)
SET @DBNAME=NULL -- Default NULL(All Databses)
select 'BackUp Name'=BS.name,
'User Name'=BS.user_name,
'Start Date'=BS.backup_start_date,
'Finish Date'=BS.backup_finish_date,
'Backup Type'=Case when BS.type='D' then 'FULL Backup'
when BS.type='L' then 'Transaction Log Backup'
when BS.type='I' then 'Differential Backup' end
,'Backup Size MB'=floor(((BS.backup_size/1024)/1024))
,'DbName'=BS.database_name
,'Server Name'=BS.server_name
,MF.physical_device_name
,'IS Ever Restored'=case when BS.backup_set_id in
(select backup_set_id from msdb.dbo.restorehistory)
then 'Yes' else 'No' end
,'Destination Db'
=isnull(RH.destination_database_name,'Yet Not Restored From This BackUpSet')
,'Restore Path'
=isnull(min(RF.destination_phys_name),'Yet Not Restored From This BackUpSet')
,'restore Type'=isnull(CASE WHEN RH.restore_type = 'D' THEN 'Database'
WHEN RH.restore_type = 'F' THEN 'File'
WHEN RH.restore_type = 'G' THEN 'Filegroup'
WHEN RH.restore_type = 'I' THEN 'Differential'
WHEN RH.restore_type = 'L' THEN 'Log'
WHEN RH.restore_type = 'V' THEN 'Verifyonly'
WHEN RH.restore_type = 'R' THEN 'Revert'
ELSE RH.restore_type
END ,'Yet Not')
,Rh.restore_date,'Restore By'=isnull(RH.user_name,'No One')
,'Time Taken'
=cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/3600 as varchar(10))
+' Hours, ' +
cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/60 as varchar(10))
+ ' Minutes, ' +
cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)%60 as varchar(10))
+' Seconds'
from msdb..backupset BS
JOIN msdb..backupmediafamily MF
on BS.media_set_id=MF.media_set_id
left outer join msdb..restorehistory RH
on BS.backup_set_id =RH.backup_set_id
left outer join msdb..restorefile RF
on RF.restore_history_id=Rh.restore_history_id
where BS.database_name = isnull(@DBNAME,BS.database_name)
group by BS.name,BS.user_name,BS.backup_start_date,BS.backup_finish_date,
BS.TYPE,BS.backup_size,BS.database_name,BS.server_name
,MF.physical_device_name,BS.backup_set_id,RH.destination_database_name
,RH.restore_type,Rh.restore_date,RH.user_name
it's really weird because I see a full backup everynight but it shows as NULL (See attachment)
January 14, 2011 at 9:00 am
Could there be some backup job that's writing to tape or the NULL device? If the diff is going down in size, you have something that is resetting the diff bitmap.
Can you check this on the day before and after the null backup?
http://www.sqlservercentral.com/articles/Administration/differentialbackupprediction/2306/
January 14, 2011 at 9:35 am
I think I figured it out.
I make a snapshot from my SAN every night @ 7:05 (which is the time all these NULL backups run) and attach it to my reporting server as a form of replication so our reporting servers are always a day behind. I don't understand why SQL sees this as a form of backup, however.
That leads me to another post on replication....
January 14, 2011 at 9:44 am
This should depend on how the SAN vendor has implemented the snapshot. They may be integrating with SQL Server.
However I'd let them know if they are marking the snapshot as a full backup, they ought to include some note in the MSDB tables so you are aware.
January 14, 2011 at 10:07 am
Hey Steve,
I use the Dell Equallogic which uses the AutoSnapShot Manager which is integrated with SQL. Ugh.
Thanks for the help guys!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply