May 13, 2009 at 10:54 am
Until recently, we have been doing a Full backup of our main production database every night. We recently changed our process to only do a Full backup twice a week, with differential backups occurring every six hours, and t-log backups every 15 minutes.
I was under the impression that diff backups capture all of the changes to the DB since the most recent Full backup, so if I restored the most recent Full backup plus the most recent differential backup, all I would then have to do is restore all t-log backups after that most recent diff restore.
In other words, if I have 1 full backup and five diff backups, I should only have to restore 2 backups -the full and the most recent diff.
However, after restoring a full backup last night, then trying to apply a diff backup - which was approximately the 6th or 7th diff after the full - I got a message saying that the diff backup could not be restored because the DB had not been restored to the proper state (that is not the exact syntax of the error...).
I could restore the diff backup that was created 1st after the full backup. I could also skip a few diffs after that one and restore another. THEN I got to a point where I couldn't restore any more diffs at all, even when I tried to restore them one by one after the Full.
Looking at the folder that contains our backups, it is evident that the diffs are not acting as I thought they would, because theire sizes will increase for a while, then the next diff will have a size much lower than the previous.
Am I missing something? Are there certain things that will 'break the chain' of diff backups other than doing a new Full backup?
Thanks in advance!
May 13, 2009 at 11:00 am
Run this query. You may find that a full backup was run between the differential backups you successfully restored and the one you didn't.
select * from msdb.dbo.backupset
May 13, 2009 at 3:10 pm
Clint (5/13/2009)
I could restore the diff backup that was created 1st after the full backup. I could also skip a few diffs after that one and restore another. THEN I got to a point where I couldn't restore any more diffs at all, even when I tried to restore them one by one after the Full.Looking at the folder that contains our backups, it is evident that the diffs are not acting as I thought they would, because theire sizes will increase for a while, then the next diff will have a size much lower than the previous.
Someone took another full backup between the diff that you can restore and the one that you can't, and they didn't use the WITH COPY option (which, if specified, doesn't reset the differential base)
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
May 14, 2009 at 8:40 am
Well, I ran this script (please forgive the formatting);
SELECT
s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4) +' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END as BackupType,
s.server_name,
s.recovery_model
FROM
msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
s.database_name = '' --replace with actual database name
ORDER BY
database_name, backup_start_date, backup_finish_date
I found that there WAS, in fact, a Full backup occurring between the backups I was doing, that I did not know about. The weird thing is, We only do Full backups for this DB on this same SQL Server instance. The are all run via maintenance plans and are scheduled by SQL Agent jobs, and I am positive that thos jobs run a Full backup only twice a week.
Is there some other process that could cause a full backup? Our network dude backs up the entire server each night via Veritas NetBackup. Would that cause the problem?
...actually, I just had our help desk guy check the Veritas NetBackup logs, and at the same time the mysterious SQL Full backup happened, there is an entry in the log for 'Default Application Backup" on that Server. I bet it is set to specifically backup SQL Server at that time. Son of gun...
I'll have to verify with our network dude when he gets back tomorrow, then I'll post agin if that's the case.
Thanks all!
May 14, 2009 at 8:49 am
The Veristas backup will likely count as a full. Note that I'd recommend you stop using that agent since it can be problematic in a DR scenario. You want to be able to restore with native tools if necessary.
Edited your code formattering with the code tags to the left of the posting box.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply