Confused about relation between full backup and transaction log backup

Hi everyone,
Would someone be able to help with a question I have about the
strangeness of my backup process/results. My schema is:
Full Backup: 2:00am each day
Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
min intervals
Full database reindex: 11:00pm on Sundays
What I found was that from Tuesday to Sunday my transactional log
backups are of minimal size. However the Monday 7:00am transactional
log backup is around the size of the database itself i.e. 30 Gigs. And
this happens each Monday 7:00am. I can only nail this down to the fact
that a full database reindex has taken place between Sunday 10:30pm and
Monday 7:00am - which is infact the case. However, my full backup takes
place on Monday 2:00am. So why should the transactional be so large? I
thought the transactional backup is a backup of:
1) transactions since the last full backup (which in my case is 2:00am
i.e. AFTER the reindex had occured - hence the 7:00am log backup should
only show transactions since the last full backup NOT the last
transactional backup) OR,
2) the last transactional backup if there has been a full backup prior
to the last log backup.
It seems as though my transactional log backup process does not depend
on my full backup process.
Does this mean that if I wanted to restore (for example) to Friday
5:00pm I could either do:
a) take a Tuesday 2:00am full backup and apply transactional backups
from Tuesday 7am to Friday 5p, OR
b) take Friday 2am full backup and apply transactional backups from
Friday 2am to Friday 5pm?
I think I am confused and hard fixed on the idea that if you implement
both full and transactional backups, the transactonal backup that takes
place immediately after a full backup contains transactions relative to
the last full backup, NOT the last transactional backup. Can anyone
confirm this?
When the answer is "relative to the last transactional backup", I guess
when you restore from the last full backup (created at 2am) and apply a
transactional backup against it (created at 7am), the system is clever
enough to disregard all transactions within the transaction log that
occured between 10:30pm and 2am - thuis only applying those
transactions between 2am and 7am. Is this correct?
Any help most appreciated in sorting out my confusion!
Many thanks,
Peter
Bottom line is that the two backups really have nothing to do with each
other except that you need a valid FULL backup to start the log chain. From
there on out you can back up the db with a FULL backup as many or as few
times as you want and the log backups always go from the last log backup.
It is a continuous chain. So yes if you had failure and the most recent FULL
backup was corrupted you can go back to the one before that and restore all
the logs up to the last one, right past where the last FULL backup was
taken. Same goes for a Differential backup. It does not affect the Log
backups at all. But unlike the Logs a Differential is always from the last
FULL backup.
Andrew J. Kelly SQL MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>
|||A full backup is a complete, consistant copy of the database at a single
point in time. If you are in FULL or BULK-LOGGED recovery mode, you can
apply one or more transaction logs in sequential order during the restore
process to move the database forward through time from the initial backup
time point. The large transaction log backup size is likely a side effect
of a reindex maintenance function.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>
|||Thanks for the response.
so in effect, if i have:
backup1: 2am transactional backup
backup2: 5am full backup
backup3: 7am transactional backup
I would like to reconfirm... am i correct in saying that:
1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
2) I can apply backup3 to backup2. In this case not all those transactions
contained in backup3 will be applied against backup2 i.e. only those
occuring between the end of backup2 and the start of backup 3 will be
applied.
Thanks again!
peter
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>
|||Yes and yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of backup1
> and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
>
|||1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
If the transaction log backups are created WITH INIT they will only contain
transactions since the previous log backup.
Keith Kratochvil
"peter" <peter@.nospam.com> wrote in message
news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of
> backup1 and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
>
|||thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OED7V6dQGHA.4312@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Yes and yes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
|||but doesnt WITH INIT just overwrite all previous log backup sets (if used
against log backups)?
So irrespective of whether WITH INIT is used or not, any transaction log
backup will contain transactions since the previous log backup?
peter
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
> 1) backup3 contains all transactions that occured between the end of
> backup1
> and the start of backup3?
> If the transaction log backups are created WITH INIT they will only
> contain transactions since the previous log backup.
> --
> Keith Kratochvil
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>
|||> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:uTek9jsQGHA.3872@.TK2MSFTNGP15.phx.gbl...
> but doesnt WITH INIT just overwrite all previous log backup sets (if used
> against log backups)?
> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
> peter
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
>