Post reply

Differential database file size

  • March 20, 2003 at 7:20 am

    #159638

    I have a full back up of a database on every Sunday and the backup file size is about 10 gigs. Then I have every weekday differential backup of the same database but by Thursday Differential Backup file size has grown to 40 gigs (even though database size is roughly 10 gigs). could some one please help me trim the differential file size. I could recover the db on a different server with no problem but I am worried about the file size.

    Thanks

    Raghu


    Raghu

  • March 20, 2003 at 7:49 am

    #451557

    This could be an easy one. A differential backup is a backup of ALL of the changes since the last FULL backup. So, your Monday differential (diff) backup is of all the changes since Sunday. Your Tuesday diff backup is all of the changes since Sunday, your Wednesday diff backup is all of the changes since Sunday......etc... Getting it? By Thursday, there have been a LOT of changes/updates/inserts/deletes etc. to the database so all of those changes equal 40 GB of data.

    Try doing a Full backup each night and a differential every four hours.

    Also, are you doing Transaction Log backups?

    -SQLBill

  • March 20, 2003 at 8:17 am

    #451566

    Could by but the differential backup doesn't log the changes of data in transactions, it only saves changed data pages. When a full backup is made, there is one page in the file that has one bit for every page of data in the database. So the full backup, turns all the bit to 0, after changes are applied to the db, the pages (or extents, i don't remember) that contain the modified data

    are set to 1. So every differential backup, backups the data pages with the bit equal to 1. If you modified always the same rows of the table, the differential backup should be with the same size.

  • March 20, 2003 at 10:37 am

    #451594

    Thanks for the replies...can I do something about it like compress the file (within Enterprise Mgr) also I am backing up my transaction logs every hour...

    I cannot backup during working hours as machines load data into the database and the way our third party software works (not labview) is that each machine has it's own dedicated load-table and it locks the table during the run.

    Raghu


    Raghu

Viewing 4 posts - 1 through 3 (of 3 total)

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