SQL Configure: backup

Showing posts with label backup. Show all posts

Saturday, 25 February 2012

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,
PeterBottom 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.googlegroups.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.googlegroups.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.googlegroups.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.googlegroups.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
>|||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.googlegroups.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!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OED7V6dQGHA.4312@.TK2MSFTNGP12.phx.gbl...
> 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.googlegroups.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
>>|||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...
>> 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.googlegroups.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
>>
>|||> 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...
>> 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.googlegroups.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
>>
>>
>

Friday, 24 February 2012

Conflict between backup and fulltext catalog

Sometimes the scheduled full backups on MY_DATABASE are not performed,
throwing the following error related with the fulltext catalog (CATALOG_XXX)
that it's used by some tables:
"The backup of the file or filegroup "sysft_CatalogXXX" is not permitted
because it is not online. BACKUP can be performed by using the FILEGROUP or
FILE clauses to restrict the selection to include only online data.
[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed."
How do I solve this error?
Thanks,
Gaspar.Are you trying to execute a backup while the fulltext catalog is being
rebuilt?
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Gaspar" <gaspar@.gaspar.com> wrote in message
news:utLKzokYGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Sometimes the scheduled full backups on MY_DATABASE are not performed,
> throwing the following error related with the fulltext catalog
> (CATALOG_XXX) that it's used by some tables:
> "The backup of the file or filegroup "sysft_CatalogXXX" is not permitted
> because it is not online. BACKUP can be performed by using the FILEGROUP
> or FILE clauses to restrict the selection to include only online data.
> [SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally.
> [SQLSTATE 42000] (Error 3013). The step failed."
> How do I solve this error?
> Thanks,
> Gaspar.
>

Conflict between backup and fulltext catalog

Sometimes the scheduled full backups on MY_DATABASE are not performed,
throwing the following error related with the fulltext catalog (CATALOG_XXX)
that it's used by some tables:
"The backup of the file or filegroup "sysft_CatalogXXX" is not permitted
because it is not online. BACKUP can be performed by using the FILEGROUP or
FILE clauses to restrict the selection to include only online data.
[SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally
.
[SQLSTATE 42000] (Error 3013). The step failed."
How do I solve this error?
Thanks,
Gaspar.Are you trying to execute a backup while the fulltext catalog is being
rebuilt?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Gaspar" <gaspar@.gaspar.com> wrote in message
news:utLKzokYGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Sometimes the scheduled full backups on MY_DATABASE are not performed,
> throwing the following error related with the fulltext catalog
> (CATALOG_XXX) that it's used by some tables:
> "The backup of the file or filegroup "sysft_CatalogXXX" is not permitted
> because it is not online. BACKUP can be performed by using the FILEGROUP
> or FILE clauses to restrict the selection to include only online data.
> [SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormal
ly.
> [SQLSTATE 42000] (Error 3013). The step failed."
> How do I solve this error?
> Thanks,
> Gaspar.
>

Sunday, 12 February 2012

Configuring a failsafe or backup server

I am not sure exactly which newsgroup to post this to so I will ask here
with the hopes that someone either can answer or at least point me to a
better newsgroup to pose the question.
We have a single SQL Server 2000 server machine. We are increasingly adding
more and more db's and applications and more and more users are depending on
this data being available. We have a sales force and dealers that access the
data remotely 24/7 from varios time zones. Now I am starting to think in
terms of data availability - making sure that if the server goes down or we
need to take the server offline for any period of time, we can move the db's
over either manually or automatically to another server. We do also have a
Win2K small business server that includes SQL server - and it is really
sitting idle so we could use that machine as the backup SQL server in a
pinch. Then we'd have to repoint all users to the new machine which would
allow them to be back up and running again in a decent time frame. Then when
we are back online with the actual SQL Server, the db would have to be
migrated back over...
There are a couple of options I am thinking...
1) to implement a complete failover system so that if one server goes down,
up comes the other one almost instantly? I doubt we really need to go to
this level but perhaps someone could elaborate on exactly what
hardware/software would be needed to implement such a scheme. I am thinking
Win2K3 R2 DFS or a Doubletake solution?
2) Manually backup and restore from one machine to the other and back again
when the other machine is back on line. This would mean that if the SQL
server went down in the afternoon, and we were unable to access the drives
for whatever reason, we would have to restore from the previous nights SQL
server backup (on disk). meaning a loss of almost a full days work
potentially..
What other options are there out there? What have other folks been doing?
Should we be backing up transaction logs every hour possibly? That way we
could restore from previous night and then play back all the transaction
logs to get current within an hour at least...
Thanks, Brad
Log shipping is another option for maintaining a warm
standby. You can write your own and don't necessarily need
to be using SQL Server 2000 Enterprise Edition. That edition
just gives you a GUI to setup and monitor the log shipping.
There are other options - depending on the business needs.
This article does a good job of going though the different
options and their advantages, disadvantages:
http://vyaskn.tripod.com/maintaining_standby_sql_server.htm
-Sue
On Mon, 28 May 2007 16:34:34 -0400, "Brad Pears"
<bradp@.truenorthloghomes.com> wrote:

>I am not sure exactly which newsgroup to post this to so I will ask here
>with the hopes that someone either can answer or at least point me to a
>better newsgroup to pose the question.
>We have a single SQL Server 2000 server machine. We are increasingly adding
>more and more db's and applications and more and more users are depending on
>this data being available. We have a sales force and dealers that access the
>data remotely 24/7 from varios time zones. Now I am starting to think in
>terms of data availability - making sure that if the server goes down or we
>need to take the server offline for any period of time, we can move the db's
>over either manually or automatically to another server. We do also have a
>Win2K small business server that includes SQL server - and it is really
>sitting idle so we could use that machine as the backup SQL server in a
>pinch. Then we'd have to repoint all users to the new machine which would
>allow them to be back up and running again in a decent time frame. Then when
>we are back online with the actual SQL Server, the db would have to be
>migrated back over...
>There are a couple of options I am thinking...
>1) to implement a complete failover system so that if one server goes down,
>up comes the other one almost instantly? I doubt we really need to go to
>this level but perhaps someone could elaborate on exactly what
>hardware/software would be needed to implement such a scheme. I am thinking
>Win2K3 R2 DFS or a Doubletake solution?
>2) Manually backup and restore from one machine to the other and back again
>when the other machine is back on line. This would mean that if the SQL
>server went down in the afternoon, and we were unable to access the drives
>for whatever reason, we would have to restore from the previous nights SQL
>server backup (on disk). meaning a loss of almost a full days work
>potentially..
>What other options are there out there? What have other folks been doing?
>Should we be backing up transaction logs every hour possibly? That way we
>could restore from previous night and then play back all the transaction
>logs to get current within an hour at least...
>Thanks, Brad
>
|||Thanks for that...
Brad
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:vbkm53lfq2197a3ah6tfasd2q2ie875n62@.4ax.com...
> Log shipping is another option for maintaining a warm
> standby. You can write your own and don't necessarily need
> to be using SQL Server 2000 Enterprise Edition. That edition
> just gives you a GUI to setup and monitor the log shipping.
> There are other options - depending on the business needs.
> This article does a good job of going though the different
> options and their advantages, disadvantages:
> http://vyaskn.tripod.com/maintaining_standby_sql_server.htm
> -Sue
> On Mon, 28 May 2007 16:34:34 -0400, "Brad Pears"
> <bradp@.truenorthloghomes.com> wrote:
>

Configuring a failsafe or backup server

I am not sure exactly which newsgroup to post this to so I will ask here
with the hopes that someone either can answer or at least point me to a
better newsgroup to pose the question.
We have a single SQL Server 2000 server machine. We are increasingly adding
more and more db's and applications and more and more users are depending on
this data being available. We have a sales force and dealers that access the
data remotely 24/7 from varios time zones. Now I am starting to think in
terms of data availability - making sure that if the server goes down or we
need to take the server offline for any period of time, we can move the db's
over either manually or automatically to another server. We do also have a
Win2K small business server that includes SQL server - and it is really
sitting idle so we could use that machine as the backup SQL server in a
pinch. Then we'd have to repoint all users to the new machine which would
allow them to be back up and running again in a decent time frame. Then when
we are back online with the actual SQL Server, the db would have to be
migrated back over...
There are a couple of options I am thinking...
1) to implement a complete failover system so that if one server goes down,
up comes the other one almost instantly? I doubt we really need to go to
this level but perhaps someone could elaborate on exactly what
hardware/software would be needed to implement such a scheme. I am thinking
Win2K3 R2 DFS or a Doubletake solution?
2) Manually backup and restore from one machine to the other and back again
when the other machine is back on line. This would mean that if the SQL
server went down in the afternoon, and we were unable to access the drives
for whatever reason, we would have to restore from the previous nights SQL
server backup (on disk). meaning a loss of almost a full days work
potentially..
What other options are there out there? What have other folks been doing?
Should we be backing up transaction logs every hour possibly? That way we
could restore from previous night and then play back all the transaction
logs to get current within an hour at least...
Thanks, BradLog shipping is another option for maintaining a warm
standby. You can write your own and don't necessarily need
to be using SQL Server 2000 Enterprise Edition. That edition
just gives you a GUI to setup and monitor the log shipping.
There are other options - depending on the business needs.
This article does a good job of going though the different
options and their advantages, disadvantages:
http://vyaskn.tripod.com/maintainin..._sql_server.htm
-Sue
On Mon, 28 May 2007 16:34:34 -0400, "Brad Pears"
<bradp@.truenorthloghomes.com> wrote:

>I am not sure exactly which newsgroup to post this to so I will ask here
>with the hopes that someone either can answer or at least point me to a
>better newsgroup to pose the question.
>We have a single SQL Server 2000 server machine. We are increasingly adding
>more and more db's and applications and more and more users are depending o
n
>this data being available. We have a sales force and dealers that access th
e
>data remotely 24/7 from varios time zones. Now I am starting to think in
>terms of data availability - making sure that if the server goes down or we
>need to take the server offline for any period of time, we can move the db'
s
>over either manually or automatically to another server. We do also have a
>Win2K small business server that includes SQL server - and it is really
>sitting idle so we could use that machine as the backup SQL server in a
>pinch. Then we'd have to repoint all users to the new machine which would
>allow them to be back up and running again in a decent time frame. Then whe
n
>we are back online with the actual SQL Server, the db would have to be
>migrated back over...
>There are a couple of options I am thinking...
>1) to implement a complete failover system so that if one server goes down,
>up comes the other one almost instantly? I doubt we really need to go to
>this level but perhaps someone could elaborate on exactly what
>hardware/software would be needed to implement such a scheme. I am thinking
>Win2K3 R2 DFS or a Doubletake solution?
>2) Manually backup and restore from one machine to the other and back again
>when the other machine is back on line. This would mean that if the SQL
>server went down in the afternoon, and we were unable to access the drives
>for whatever reason, we would have to restore from the previous nights SQL
>server backup (on disk). meaning a loss of almost a full days work
>potentially..
>What other options are there out there? What have other folks been doing?
>Should we be backing up transaction logs every hour possibly? That way we
>could restore from previous night and then play back all the transaction
>logs to get current within an hour at least...
>Thanks, Brad
>|||Thanks for that...
Brad
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:vbkm53lfq2197a3ah6tfasd2q2ie875n62@.
4ax.com...
> Log shipping is another option for maintaining a warm
> standby. You can write your own and don't necessarily need
> to be using SQL Server 2000 Enterprise Edition. That edition
> just gives you a GUI to setup and monitor the log shipping.
> There are other options - depending on the business needs.
> This article does a good job of going though the different
> options and their advantages, disadvantages:
> http://vyaskn.tripod.com/maintainin..._sql_server.htm
> -Sue
> On Mon, 28 May 2007 16:34:34 -0400, "Brad Pears"
> <bradp@.truenorthloghomes.com> wrote:
>
>

Saturday, 25 February 2012

Confused about relation between full backup and transaction log backup

Confused about relation between full backup and transaction log backup

Confused about relation between full backup and transaction log backup

Friday, 24 February 2012

Conflict between backup and fulltext catalog

Conflict between backup and fulltext catalog

Sunday, 12 February 2012

Configuring a failsafe or backup server

Configuring a failsafe or backup server

SQL Configure

Blog Archive

About Me