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?
>
>

Differential backup "standard" file extension?

Hello. Just wondering if there's a MS standard for file
extensions for Differential backups? Like a BAK file is a
Full BAckup and a TRN is a Trans Log backup. I'm using a
DIF filename extension currently, but would like to use a
common file extension, if there is one?!? THanks, BruceI believe it's probably just .bak but your way sounds great
for easily identifying differential backups. As with the data
and log files themselves you can give them any extension
you want and they will work - MDF/NDF/LDF are just
the defaults.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:017801c34bb2$d906f6c0$a101280a@.phx.gbl...
Hello. Just wondering if there's a MS standard for file
extensions for Differential backups? Like a BAK file is a
Full BAckup and a TRN is a Trans Log backup. I'm using a
DIF filename extension currently, but would like to use a
common file extension, if there is one?!? THanks, Bruce|||THanks Jasper. Yes, was jsut looking for standard
defaults, like MS does with BAK and TRN. I wouldn't want
to call a Differential backup and BAK file... ok, new
standard>> DIF... ha... THanks, Bruce
>--Original Message--
>I believe it's probably just .bak but your way sounds
great
>for easily identifying differential backups. As with the
data
>and log files themselves you can give them any extension
>you want and they will work - MDF/NDF/LDF are just
>the defaults.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:017801c34bb2$d906f6c0$a101280a@.phx.gbl...
>Hello. Just wondering if there's a MS standard for file
>extensions for Differential backups? Like a BAK file is a
>Full BAckup and a TRN is a Trans Log backup. I'm using a
>DIF filename extension currently, but would like to use a
>common file extension, if there is one?!? THanks, Bruce
>
>.
>

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...

quote:

---

> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>

---

|||

quote:

---

>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>

---

Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.

quote:

---

>AK
>
>.
>

---

|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK

quote:

---

>--Original Message--
>I wouldn't figure the differential should ever be larger

---

than the full DB

quote:

---

>backup. Is the differential backup writing to the same

---

file each time? If so

quote:

---

>then you're probably adding to the file each time, look

---

up the WITH FORMAT

quote:

---

>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
in[QUOTE]
>
>.
>

---

|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK

quote:

---

>--Original Message--
>
in[QUOTE]
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>.
>

---

|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...[QUOTE]
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
>
> than the full DB
> file each time? If so
> up the WITH FORMAT
> message
> in