Backup

Hi,
I have a question regarding backup...
I doing a online backup,my database is like 600GB.
During the online backup , i inserting data into the
database at the same time through DTS package...which will
run for 12hrs...
Can anybody tell me wht will be the affect on the online
backup?
Thanks
JenifferThere are only two real considerations for loading and backing up data at
the same time.
First, the transaction log cannot truncate during a backup. This is due to
the nature of the 'fuzzy backup' algorithm that SQL server uses. Second,
any disk subsystem has a finite amount of I/O capacity. That capacity will
have to be shared between backup and loading. I suggest running them
separately as a test to establish baseline performance and then running them
together to see if the combination is better or worse performing.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"jeniffer" <df45g2c@.yahoo.com> wrote in message
news:00d601c3552e$441eed80$a401280a@.phx.gbl...
> Hi,
> I have a question regarding backup...
> I doing a online backup,my database is like 600GB.
> During the online backup , i inserting data into the
> database at the same time through DTS package...which will
> run for 12hrs...
> Can anybody tell me wht will be the affect on the online
> backup?
> Thanks
> Jeniffer
>|||Thanks for the reply...
I would like to know whether the data which loading will
also be backed Up?
Jeniffer
>--Original Message--
>There are only two real considerations for loading and
backing up data at
>the same time.
>First, the transaction log cannot truncate during a
backup. This is due to
>the nature of the 'fuzzy backup' algorithm that SQL
server uses. Second,
>any disk subsystem has a finite amount of I/O capacity.
That capacity will
>have to be shared between backup and loading. I suggest
running them
>separately as a test to establish baseline performance
and then running them
>together to see if the combination is better or worse
performing.
>--
>Geoff N. Hiten
>SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>
>
>"jeniffer" <df45g2c@.yahoo.com> wrote in message
>news:00d601c3552e$441eed80$a401280a@.phx.gbl...
>> Hi,
>> I have a question regarding backup...
>> I doing a online backup,my database is like 600GB.
>> During the online backup , i inserting data into the
>> database at the same time through DTS package...which
will
>> run for 12hrs...
>> Can anybody tell me wht will be the affect on the online
>> backup?
>> Thanks
>> Jeniffer
>>
>
>.
>|||Backups are transactionally consistant to the end of the backup time period.
Any transactions that are committed before the log portion of the backup
begins are included.
Here is a quick description of how a backup works: First, backup grabs a
copy of the allocation maps for the database in question. It also writes a
marker in the transaction log so it won't get truncated. The backup then
writes all the allocated extents to the backup device. Finally, backup
copies (but does not truncate) a portion of the log covering the
transactions that happened while the backup was running. All of this is
included in the single backup file along with information about file sizes
and locations. Thus, a restore will give you a copy of the data at a
specific point near to the end of the backup time.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jeniffer" <df45g2c@.yahoo.com> wrote in message
news:02df01c35531$75d33400$a101280a@.phx.gbl...
> Thanks for the reply...
> I would like to know whether the data which loading will
> also be backed Up?
> Jeniffer
> >--Original Message--
> >There are only two real considerations for loading and
> backing up data at
> >the same time.
> >
> >First, the transaction log cannot truncate during a
> backup. This is due to
> >the nature of the 'fuzzy backup' algorithm that SQL
> server uses. Second,
> >any disk subsystem has a finite amount of I/O capacity.
> That capacity will
> >have to be shared between backup and loading. I suggest
> running them
> >separately as a test to establish baseline performance
> and then running them
> >together to see if the combination is better or worse
> performing.
> >
> >--
> >Geoff N. Hiten
> >SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >
> >
> >
> >"jeniffer" <df45g2c@.yahoo.com> wrote in message
> >news:00d601c3552e$441eed80$a401280a@.phx.gbl...
> >> Hi,
> >> I have a question regarding backup...
> >> I doing a online backup,my database is like 600GB.
> >> During the online backup , i inserting data into the
> >> database at the same time through DTS package...which
> will
> >> run for 12hrs...
> >>
> >> Can anybody tell me wht will be the affect on the online
> >> backup?
> >>
> >> Thanks
> >> Jeniffer
> >>
> >>
> >
> >
> >.
> >|||Thanks...that was good explanation.
Jeniffer
>--Original Message--
>Backups are transactionally consistant to the end of the
backup time period.
>Any transactions that are committed before the log
portion of the backup
>begins are included.
>Here is a quick description of how a backup works:
First, backup grabs a
>copy of the allocation maps for the database in
question. It also writes a
>marker in the transaction log so it won't get truncated.
The backup then
>writes all the allocated extents to the backup device.
Finally, backup
>copies (but does not truncate) a portion of the log
covering the
>transactions that happened while the backup was running.
All of this is
>included in the single backup file along with information
about file sizes
>and locations. Thus, a restore will give you a copy of
the data at a
>specific point near to the end of the backup time.
>--
>Geoff N. Hiten
>SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>
>
>"Jeniffer" <df45g2c@.yahoo.com> wrote in message
>news:02df01c35531$75d33400$a101280a@.phx.gbl...
>> Thanks for the reply...
>> I would like to know whether the data which loading will
>> also be backed Up?
>> Jeniffer
>> >--Original Message--
>> >There are only two real considerations for loading and
>> backing up data at
>> >the same time.
>> >
>> >First, the transaction log cannot truncate during a
>> backup. This is due to
>> >the nature of the 'fuzzy backup' algorithm that SQL
>> server uses. Second,
>> >any disk subsystem has a finite amount of I/O capacity.
>> That capacity will
>> >have to be shared between backup and loading. I
suggest
>> running them
>> >separately as a test to establish baseline performance
>> and then running them
>> >together to see if the combination is better or worse
>> performing.
>> >
>> >--
>> >Geoff N. Hiten
>> >SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >
>> >
>> >
>> >"jeniffer" <df45g2c@.yahoo.com> wrote in message
>> >news:00d601c3552e$441eed80$a401280a@.phx.gbl...
>> >> Hi,
>> >> I have a question regarding backup...
>> >> I doing a online backup,my database is like 600GB.
>> >> During the online backup , i inserting data into the
>> >> database at the same time through DTS package...which
>> will
>> >> run for 12hrs...
>> >>
>> >> Can anybody tell me wht will be the affect on the
online
>> >> backup?
>> >>
>> >> Thanks
>> >> Jeniffer
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>