My database is 30Gb in size. Full DB backup takes 30Gb and Differential
backup only 24 hrs later takes... 29.5Gb!
There is no heavy activity on database, 95% of database pages could not be
changed over 24 hr period.
Any advise on Differential backup size? It shouldn't be as big as Full DB
backup.
Thanks,
Leon ShargorodskyMy guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky|||A differential backup backs up all modified pages since the last full. If
you have modified the data, by any mechanism, then they would be written to
the differential backup. So, look for CRUD operations against the database
including index defrag, index rebuild, and database shrink operations, which
re-write index and/or data pages and are recorded differences that will
accumulate in the differential backup.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0ejm7UCFHA.560@.TK2MSFTNGP15.phx.gbl...
My guess is that you have performed either reindexing or a shrink operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
news:D99B2F64-6F5F-4552-B99F-5806477B8783@.microsoft.com...
> My database is 30Gb in size. Full DB backup takes 30Gb and Differential
> backup only 24 hrs later takes... 29.5Gb!
> There is no heavy activity on database, 95% of database pages could not be
> changed over 24 hr period.
> Any advise on Differential backup size? It shouldn't be as big as Full DB
> backup.
> Thanks,
> Leon Shargorodsky
Showing posts with label 5gb. Show all posts
Showing posts with label 5gb. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
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
> >>
> >>
> >
> >
> >.
> >
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...
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
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
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
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
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:|||Mike:
>AK
>
>.
>
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:|||How would I NOT append and rather overwrite?
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
in[QUOTE]
>
>.
>
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
quote:|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
>--Original Message--
>
in[QUOTE]
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>.
>
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
Subscribe to:
Posts (Atom)