Differential backups as large as actual backup

Question

Differential backups as large as actual backup

sbauer

SSCommitted

Points: 1906
More actions
June 1, 2004 at 12:01 pm

#76166

We have a job with two steps. The first is performing a full backup of the database once a week. The second step performs a differential backup with an 'init' right after the full backup. We were expecting the first differential to be small as there weren't many changes to the database since the full backup. However, the differential is about the same size as the full backup.
We then have a second job where we perform the daily differential 's which are scheduled for the rest of the week. The rest of the differential's are relatively small. Why is the first differential with an 'INIT', right after the full backup so big?
Here are the jobs
1st job
1st step of frist job
backup database report to report1, report2 , report3,
report4
with format
2nd step of first job
BACKUP DATABASE report
   TO reportdiffs1,reportdiffs2
   WITH init, DIFFERENTIAL
GO
1st and only step of second job is run nightly
BACKUP DATABASE report
   TO reportdiffs1,reportdiffs2
   WITH init, DIFFERENTIAL
GO
Any help would be greatly appreciated.
Thanks
Susan

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Don't quote me, but it's possible that you are doing the differential too soon and the full backup has not 'marked' the database as being backed up. So the first diff you are doing is actually the records of BEFORE the full backup is done.

Just a guess.

-SQLBill

Tab Alleman SSCommitted Points: 1590 More actions · Answer 2

Do you have a "GO" between the first and second steps of the first job? If not, try putting one in.

(Also a guess)

iLoveSQL SSCrazy Points: 2888 More actions · Answer 3

I'd try use SKIP and NOFORMAT option for that even though SKIP is for checking expired date, I usually put it there for initializing ones. Might not have to be there but it works anyway.

BACKUP DATABASE report

TO reportdiffs1,reportdiffs2

WITH init, DIFFERENTIAL, SKIP ,NOFORMAT

GO

lholman SSCrazy Points: 2810 More actions · Answer 4

hey susan,

i think sqlbill's on the right track. we had a similar problem within a multistep job where the first step of the job was to back up the database then subsequent steps ran weekly upload processing. when we went to restore to one of those backups (thought to be pre-weekly upload processing) we found that the majority of the upload processing had been applied to the database. remember full backups give you a backup that is identical to the state of the database at the time of completion of the backup process. maybe your differential backup is kicking off too early as sqlbill suggested? have you tried looking at the job history within EM and switching on 'Show step details' to see when each step starts and it's duration?

lloyd

evorstenbosch SSC Enthusiast Points: 127 More actions · Answer 5

I have had a similar problem with a large first transaction log backup. It turned out that, as part of the maintenance plan, there was a rebuild index step just before the full backup.

By adding a truncate log step just before the full backup, the problem was solved.

Maybe this will help.

Erik