| Author |
Topic |
|
YogeshDesai
Posting Yak Master
136 Posts |
 Posted - 2008-02-01 : 09:48:24
|
| Hi Guys,I am facing some issue with Differential backup.I have some doubts about the diff backup size, As per the concept diff backup means only the modification after the full backup.In my organization I have set the backup plan that is weekly full backup which happen on sunday and daily diff backup.I have taken the ful backup of one database which size is showing me 3.77GB and when I took the diff backup on friday it was showing me the size 3.68GB for diff backup.How it is possible ? Can any one help me regarding this ?I tried the full backup and diff backup using scripts and as well as EM but still facing the same problem, I am getting the big diff backupsSQL IN Minds |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-01 : 13:00:06
|
| You've got that size as that's how many changes have occurred since the last full backup. Taking daily diffs doesn't help with the size much as diffs are the changes since the last full and not changes since the last full or diff. So either run full backups more frequently or switch to tlog backups instead.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-02-01 : 13:33:04
|
| it also sounds as if a lot of data is changing in the database. are you doing full data loads each day or something? -ec |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-01 : 22:42:45
|
| Diff. backup not just contains changed data. If you modify one row, sql will backup whole extent the row resides in. If that row has relation to other table, will backup more extents. |
|
|
|
YogeshDesai
Posting Yak Master
136 Posts |
Posted - 2008-02-05 : 07:01:41
|
| hey guys, we are running optimization job on daily basis which cause to increase the size of differential backups, to observer it I kept optimization job on ice and observe the differential backup size it's reduced into mb.Do you know any exact reason why this happen, actually i want to understand the logic behind thisSQL IN Minds |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-02-05 : 11:51:58
|
quote:
Originally posted by YogeshDesai
hey guys, we are running optimization job on daily basis which cause to increase the size of differential backups, to observer it I kept optimization job on ice and observe the differential backup size it's reduced into mb.Do you know any exact reason why this happen, actually i want to understand the logic behind thisSQL IN Minds
you are running an optimization job every day? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-05 : 22:48:55
|
| That rebuilds indexes, will generate log of logs. |
|
|
|
kmfarooqi
Starting Member
7 Posts |
Posted - 2008-04-21 : 02:49:02
|
| i do have the same problem as you stated above (differential backup size is same as full backup size). can you please help me to solve this problem.note: i have several different databases which are doing good when i take their differential back up (less in size) except only one database.thanking you in advance.Khader |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-21 : 22:20:14
|
| Check response in your own thread. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-21 : 22:59:38
|
| If you are rebuilding all clustered indexes that will probably update every extent and so the diff will be the same size as the full backuop.If you're only 3.7 GB do you need to do diff backups? Can you just do all full?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Differential backup Size Question