• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

MS SQL 7.0 Diff Backup

I have setup a SQL job to do a full backup of the dB every week. When I check the backup file size it's 3.6 GB. I also have a differential backup running every day (It writes to a backup device). I found that the diff backup file size is 3.5 GB!.

I was expecting the diff file size to be much less than the full backup file. The dB does not grow that much every week!. It's supposed to grow only ~ 8 MB every week, I know that as a fact.

How can the diff backup file size be so close from the full backup file size? Something is not right!

Thanks



0
novice12
Asked:
novice12
  • 3
  • 2
1 Solution
 
ShogunWadeCommented:
The differential backup size depends on the volume of data that has changed since the last full backup.    I would have as a guess that the vast majority of your data is bein modified during the week.

0
 
novice12Author Commented:
Here is the job step:

BACKUP DATABSE [DatabseName] TO [BackupDeviceName] with INIT, NOUNLOAD, DIFFERENTIAL, NAME = N'JOBNAME', NOSKIP, NOFORMAT
0
 
ShogunWadeCommented:
As I mentioned though :

The differential backup size depends on the volume of data that has changed since the last full backup.    I would have as a guess that the vast majority of your data is bein modified during the week.
0
 
novice12Author Commented:
Thanks for the reply.

In the same Maintenance Plan that does the full backup, I found that there are other scheduled jobs. It does dB optimization and integrity check every day.

I guess this explains why the diff backup is so large. The optimization job is obviously altering almost the entire dB.

Another question I have: Is it a common practice to do optimization and integrity check that often?.
0
 
ShogunWadeCommented:
Its difficult to say how frequently you should do optimisation.    It all really depends on just how "dynamic" your data is.   If it is pretty static (ie: mainly read only) then daily is deffinately too much.   on the otherhand  a database where the distrubution of data is changing rapidly  needs frequent optimisation.  

I think if i were you i would start off with say ding it once a week.   if you find the database slows considerably (which i doubt it will)  then increase the frequency, if there is no performance loss decrease the frequency.   Eventually over time you will strike a nice balance.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now