Differential backup file almost as big as a full backup

We've been during daily full backups, with the size of the bak files at
slightly under 1.4G. The last full backup was Tuesday night. On Wednesday,
I did my first diffential backup, and the file size was 1.25G.
We are a very small company and there is little daily input. Yesterday, we
might have received a couple of checks, paid a couple bills, ordered a couple
parts, entered a few timecard transactions. So I really expected the
differential backup to show a substantial savings in disk space.
In looking for reasons:
1. The backup plan is put together using the maintenance plan wizard, and
one of the things it does is create separate files that bear the database
name, date and time. Is it possible that Sql 2005 doesn't realize that there
was a previous full backup if the differential is being put in a different
file?
2. As part of the nightly maintenance, I run tasks to check integrity,
rebuid indexes and update statistics. Could these tasks be making database
changes that would therefore cause practically the entire database to be
included in the differential backup, even though the actual data has not
changed?I would suggest that you take a copy of the database and run some backups
manually. Your logic is sound, but your results don't make sense.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> We've been during daily full backups, with the size of the bak files at
> slightly under 1.4G. The last full backup was Tuesday night. On
> Wednesday,
> I did my first diffential backup, and the file size was 1.25G.
> We are a very small company and there is little daily input. Yesterday,
> we
> might have received a couple of checks, paid a couple bills, ordered a
> couple
> parts, entered a few timecard transactions. So I really expected the
> differential backup to show a substantial savings in disk space.
> In looking for reasons:
> 1. The backup plan is put together using the maintenance plan wizard, and
> one of the things it does is create separate files that bear the database
> name, date and time. Is it possible that Sql 2005 doesn't realize that
> there
> was a previous full backup if the differential is being put in a different
> file?
> 2. As part of the nightly maintenance, I run tasks to check integrity,
> rebuid indexes and update statistics. Could these tasks be making
> database
> changes that would therefore cause practically the entire database to be
> included in the differential backup, even though the actual data has not
> changed?|||Bev,
Yes, rebuilding indexes updates your database. So every page (or is it
extent) that is updated by the process will be backed up. Doing all of that
nightly is not compatible with getting small differential backups.
With very low velocity of data change, the nightly index rebuilds should not
be necessary.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> We've been during daily full backups, with the size of the bak files at
> slightly under 1.4G. The last full backup was Tuesday night. On
> Wednesday,
> I did my first diffential backup, and the file size was 1.25G.
> We are a very small company and there is little daily input. Yesterday,
> we
> might have received a couple of checks, paid a couple bills, ordered a
> couple
> parts, entered a few timecard transactions. So I really expected the
> differential backup to show a substantial savings in disk space.
> In looking for reasons:
> 1. The backup plan is put together using the maintenance plan wizard, and
> one of the things it does is create separate files that bear the database
> name, date and time. Is it possible that Sql 2005 doesn't realize that
> there
> was a previous full backup if the differential is being put in a different
> file?
> 2. As part of the nightly maintenance, I run tasks to check integrity,
> rebuid indexes and update statistics. Could these tasks be making
> database
> changes that would therefore cause practically the entire database to be
> included in the differential backup, even though the actual data has not
> changed?|||That is just what my tests have shown me. I have removed that task from the
plan and hope to see better results in the morning. There was no particular
reason I was doing that anyway, just an erroneous belief that you can't have
too much of a good thing.
"Russell Fields" wrote:
> Bev,
> Yes, rebuilding indexes updates your database. So every page (or is it
> extent) that is updated by the process will be backed up. Doing all of that
> nightly is not compatible with getting small differential backups.
> With very low velocity of data change, the nightly index rebuilds should not
> be necessary.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:330C9832-3606-4037-8CB5-F01BDFE8C505@.microsoft.com...
> > We've been during daily full backups, with the size of the bak files at
> > slightly under 1.4G. The last full backup was Tuesday night. On
> > Wednesday,
> > I did my first diffential backup, and the file size was 1.25G.
> > We are a very small company and there is little daily input. Yesterday,
> > we
> > might have received a couple of checks, paid a couple bills, ordered a
> > couple
> > parts, entered a few timecard transactions. So I really expected the
> > differential backup to show a substantial savings in disk space.
> >
> > In looking for reasons:
> > 1. The backup plan is put together using the maintenance plan wizard, and
> > one of the things it does is create separate files that bear the database
> > name, date and time. Is it possible that Sql 2005 doesn't realize that
> > there
> > was a previous full backup if the differential is being put in a different
> > file?
> >
> > 2. As part of the nightly maintenance, I run tasks to check integrity,
> > rebuid indexes and update statistics. Could these tasks be making
> > database
> > changes that would therefore cause practically the entire database to be
> > included in the differential backup, even though the actual data has not
> > changed?
>
>