SQL Server Performance Forum – Threads Archive

Very large Differential Backup

I am using SQL Server 2005 SP1 and I have a production database (12GB).
My database has a huge table taking about 65% of its size. A lot of insert- statements are executed in this table each day. I developed two SQL Server Integration packages, the first backups my database on Saturday – 12 AM (full backup) and the second backups my database every night-12 AM (Differential backup) except Saturday. Both SSIS packages store the backup file to a USB-attached hard disk drive.
The full backup lasts about 3 hours (although the there is almost no traffic to the database, no command execution) and the file has a size of about 11 GB.
The problem is that the differential backup lasts about 2 hours and 30 minutes and the size of the produced file is 9GB !!!! What causes the differential backup to be so large? The large table of my db(65% of db size) grows every day but not all of its pages (this table usually gets a lot of insert and select statements).
In this case, the differential backup backups only the pages of the table that changed or all the pages of the table? Do you believe that using a USB-hard disk drive (instead of saving the backup to local hard disk drive) has impact to the backup duration?What may be the reason that the backups last almost 3 hours since there is almost no traffic to the server and to the database?

---

Why don’t you perform Transaction log backup schedule say atleast every half-an-hour to keepup the size of transaction log and backup. Also check whether any chance of keeping the inserts in smaller batches and might consider to perform frequent differential backups. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

I must keep the db to SIMPLE recovery model and I dont need to have t-log backups.The insert statements come from my customers applications so I cant stop or reduce them. Can you suggest any possible reason for the large size of my differential backup?

---

probably has to do with the choice of cluster key on the big table
let me guess: is it a guid? anyways, if diff bu gets big, you should just do another full backup
also consider backup to local SCSI/SATA disk first, then copy to usb
3hr for 12gb is silly

---

joechang and satya,
first of all thank you very much for your help.
I run again the differential backup and I realized that the reason for the delay was the usb 1.1.
I tried to take differential backup to the local hard disk drive and lasted about 20-30 minutes.
But, the size of the differential backup file is very big again (almost 9 GB).
The cluster key on the big table is a datetime column.
Any ideas about the reasons that caused this size?

---

is the PK a sequentially increasing datetime value?
if so, i see no reason the diff backup is so large. in this case, doing the diff backup is worthless because to restore, you have to start with a full, then the diff.
just do full backups,
consider a backup compression product as well. Quest, Idera and some other company that pays for a BS performance report have this

---

every night i execute a rebuild index task. Do you believe that this task affects all the pages?
Is this the reason that the differential is so large?

---

yes,
make the rebuild index a weekly or even monthy task,
do a full backup after the rebuild index
then do diff backup daily,

---

Perform this operation in quiet time in order to avoid other problems. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>