Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
--
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> > Differential and Transaction backup restore.
> >
> >
> >
> > I have scheduled differential backup to run at 3PM and the transactional
> > backup to run at 3pm. In the event of data restore work, should I
restore
> > the data from differential as well as transactional backups.
> >
> > My questions, is it required to run data restore from the 3Pm
> > transactional
> > backup?
> >
> > Thanks,
> >
> > Steve
> >
> >
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
> you
>> only issuing one Log backup during the entire day? Depending on which
> kicked
>> in first or finished first and if there was any activity during that time
> it
>> could be either one or both. Transaction logs keep a running chain of all
>> transactions since the first FULL backup assuming you never truncated the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to restore
>> the log and it is out of sequence it will warn you. I suggest you stagger
>> your backups and ensure you have a proper schedule of Log backups to cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the transactional
>> > backup to run at 3pm. In the event of data restore work, should I
> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>>I will rethink bout my backup scheduling. Thanks for the suggestions.
>>
>> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
>> truncated the log file. SQL Server crashes at 10am, then all my
>> transaction
>> log files are not restorable since 9.00 onwards. Is it correct?
>> Steve
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
>> you
>> only issuing one Log backup during the entire day? Depending on which
>> kicked
>> in first or finished first and if there was any activity during that
>> time
>> it
>> could be either one or both. Transaction logs keep a running chain of
>> all
>> transactions since the first FULL backup assuming you never truncated
>> the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
>> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to
>> restore
>> the log and it is out of sequence it will warn you. I suggest you
>> stagger
>> your backups and ensure you have a proper schedule of Log backups to
>> cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the
>> > transactional
>> > backup to run at 3pm. In the event of data restore work, should I
>> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>>
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> > WITH NO_LOG?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
> >>
> >>
> >>
> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
am
> >> truncated the log file. SQL Server crashes at 10am, then all my
> >> transaction
> >> log files are not restorable since 9.00 onwards. Is it correct?
> >>
> >> Steve
> >>
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> >> Why are you doing a Differential and a Log backup at the same time?
Are
> >> you
> >> only issuing one Log backup during the entire day? Depending on which
> >> kicked
> >> in first or finished first and if there was any activity during that
> >> time
> >> it
> >> could be either one or both. Transaction logs keep a running chain of
> >> all
> >> transactions since the first FULL backup assuming you never truncated
> >> the
> >> log outside of a log backup. The Diff has all the changes since the
last
> >> FULL backup. You always apply the last FULL backup and then the most
> >> recent
> >> Diff if you have one. Then any logs since the last Diff. Since it is
> >> imposable to tell the real order from this post it is hard to say if
> >> restoring the log would actually do anything or not in regards to
> >> transactions that the Diff may not have caught. If you attempt to
> >> restore
> >> the log and it is out of sequence it will warn you. I suggest you
> >> stagger
> >> your backups and ensure you have a proper schedule of Log backups to
> >> cover
> >> your needs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> >> > Differential and Transaction backup restore.
> >> >
> >> >
> >> >
> >> > I have scheduled differential backup to run at 3PM and the
> >> > transactional
> >> > backup to run at 3pm. In the event of data restore work, should I
> >> restore
> >> > the data from differential as well as transactional backups.
> >> >
> >> > My questions, is it required to run data restore from the 3Pm
> >> > transactional
> >> > backup?
> >> >
> >> > Thanks,
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
>> If someone actually truncates the log at 9am, you can only restore til
> 8am,
>> as you suspect.
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
>> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
>> > WITH NO_LOG?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
>> >>
>> >>
>> >>
>> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
> am
>> >> truncated the log file. SQL Server crashes at 10am, then all my
>> >> transaction
>> >> log files are not restorable since 9.00 onwards. Is it correct?
>> >>
>> >> Steve
>> >>
>> >>
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> >> Why are you doing a Differential and a Log backup at the same time?
> Are
>> >> you
>> >> only issuing one Log backup during the entire day? Depending on which
>> >> kicked
>> >> in first or finished first and if there was any activity during that
>> >> time
>> >> it
>> >> could be either one or both. Transaction logs keep a running chain of
>> >> all
>> >> transactions since the first FULL backup assuming you never truncated
>> >> the
>> >> log outside of a log backup. The Diff has all the changes since the
> last
>> >> FULL backup. You always apply the last FULL backup and then the most
>> >> recent
>> >> Diff if you have one. Then any logs since the last Diff. Since it is
>> >> imposable to tell the real order from this post it is hard to say if
>> >> restoring the log would actually do anything or not in regards to
>> >> transactions that the Diff may not have caught. If you attempt to
>> >> restore
>> >> the log and it is out of sequence it will warn you. I suggest you
>> >> stagger
>> >> your backups and ensure you have a proper schedule of Log backups to
>> >> cover
>> >> your needs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> >> > Differential and Transaction backup restore.
>> >> >
>> >> >
>> >> >
>> >> > I have scheduled differential backup to run at 3PM and the
>> >> > transactional
>> >> > backup to run at 3pm. In the event of data restore work, should I
>> >> restore
>> >> > the data from differential as well as transactional backups.
>> >> >
>> >> > My questions, is it required to run data restore from the 3Pm
>> >> > transactional
>> >> > backup?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Steve
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>