Differential Backups..

  • My differential backups are as big as full database backups. The database is in simple mode and I am just inserting around 4000 rows in one of the tables each day.

    Any idea why diff and full database backups are of same size ?

    Any help is appreciated.

    Thanks.

  • Are you appending your differential backups? Maybe you have many backups inside your backup device.

    On what schedule you are doing your diff backups?

  • I am doing a INIT, so it should not append. Also I am doing it on every night.

    And also I am not using a device, I use a file name to back it up.

    backup database dbname to disk = @path with init , differential

  • I am running a optmization job every night.

    Could that be causing my differential backup to be as large as database backup ?

    Thanks.

  • I think the optimizations rebuilds all indexes and marks them as "changed". Therefore they will be included in a differential backup.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • From memory (which ain't all that), does WITH INIT not force a new file to be created each time?

    ---------------------------------------------
    If data can screw you, it will; never assume the data are correct.

  • quote:


    I think the optimizations rebuilds all indexes and marks them as "changed". Therefore they will be included in a differential backup.


    That is correct, an Index rebuild causes data movement in the pages and thus marks the pages as changed. This is what differential looks at as a flag to know what to backup. Which really you should not need to be done everynight with only 4000 changes a day. I would consider doing this once a week or even month and at the time you do a Full Backup.

  • Thanks a lot to all you guys for your inputs.

Viewing 9 posts - 1 through 8 (of 8 total)

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