June 1, 2004 at 12:01 pm
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
June 1, 2004 at 1:42 pm
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
June 2, 2004 at 7:56 am
Do you have a "GO" between the first and second steps of the first job? If not, try putting one in.
(Also a guess)
June 2, 2004 at 3:45 pm
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
June 2, 2004 at 4:56 pm
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
June 3, 2004 at 1:42 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply