Differential backups as large as actual backup

  • 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

     

  • 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

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

    (Also a guess)

  • 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

     

     

  • 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

  • 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