Log shipping and DB corruption

Hi,
I've set up two log shipping pairs between three Microsoft SQL Server 2000
SP3 databases: a primary, a main standby, and an auxiliary standby database.
For the main standby database jobs that create, copy, and restore transaction
log backups run one after another without any delay. Now if the primary
database fails it may well be because it has become corrupt. According to
documentation on the Microsoft website and considering my set-up (i.e. no
delay for loading transaction logs), corrupt data may be carried over into
the main standby databaseby means of log shipping (could you please elaborate
on how/ in which cases this can happen?) . So in order to deal with a
situation, when it's not possible to switch over to the main standby
database, because corrupt data has been carried over into it, I maintain a
lagged auxiliary standby database, which could be restored up to the moment
of corruption and used instead. The question is, how can I determine this
moment? Is it the moment a message about the corrupt primary is logged in the
SQL Server error log? Can this moment be determined at all? How would you
deal with the situation I described?
Respectfully,
OskarOskar
First of all yes it will be logged into ERROR.LOG . At my work place I run
DBCC CHECKDB on regular period.
If the database get corrupted but users still have an access ,so run DBCC
CHECKDB (see more in the BOL) .Personally I have not tried to run this
command on "mirrored" database , so worth to test.
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby
> database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the
> moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>|||Very often, the cause is data corruption is the user or administration. For
example, someone *thought* they were in test but they were in prod and
deleted or modified data. It's very important to know when the data was
corrupted because that is the point at which you want to stop restoring your
log.
If you copy the log over to your standby server right after it is backed up,
that's a good thing. Putting a delay of the restoration of the log gives
you a bit of time to investigate. At the very least, you can restore WITH
STANDBY to have a look at what the data looked like and then copy that data
back to the primary.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
Hi,
I've set up two log shipping pairs between three Microsoft SQL Server 2000
SP3 databases: a primary, a main standby, and an auxiliary standby database.
For the main standby database jobs that create, copy, and restore
transaction
log backups run one after another without any delay. Now if the primary
database fails it may well be because it has become corrupt. According to
documentation on the Microsoft website and considering my set-up (i.e. no
delay for loading transaction logs), corrupt data may be carried over into
the main standby databaseby means of log shipping (could you please
elaborate
on how/ in which cases this can happen?) . So in order to deal with a
situation, when it's not possible to switch over to the main standby
database, because corrupt data has been carried over into it, I maintain a
lagged auxiliary standby database, which could be restored up to the moment
of corruption and used instead. The question is, how can I determine this
moment? Is it the moment a message about the corrupt primary is logged in
the
SQL Server error log? Can this moment be determined at all? How would you
deal with the situation I described?
Respectfully,
Oskar|||Thanks Tom. Though by the word "corruption" here I meant the corruption of
SQL Server's internal storage structures (for lack of a better name),
detected by the CHECKDB command, not the application or user negligence.
The question still remains, how can I determine the moment, just before
corruption was loaded into the main standby database, after which all the
remaining transaction log backups are to be discarded?
"Tom Moreau" wrote:
> Very often, the cause is data corruption is the user or administration. For
> example, someone *thought* they were in test but they were in prod and
> deleted or modified data. It's very important to know when the data was
> corrupted because that is the point at which you want to stop restoring your
> log.
> If you copy the log over to your standby server right after it is backed up,
> that's a good thing. Putting a delay of the restoration of the log gives
> you a bit of time to investigate. At the very least, you can restore WITH
> STANDBY to have a look at what the data looked like and then copy that data
> back to the primary.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>|||The error log is your best bet - if SQL Server detected the corruption.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
Thanks Tom. Though by the word "corruption" here I meant the corruption of
SQL Server's internal storage structures (for lack of a better name),
detected by the CHECKDB command, not the application or user negligence.
The question still remains, how can I determine the moment, just before
corruption was loaded into the main standby database, after which all the
remaining transaction log backups are to be discarded?
"Tom Moreau" wrote:
> Very often, the cause is data corruption is the user or administration.
> For
> example, someone *thought* they were in test but they were in prod and
> deleted or modified data. It's very important to know when the data was
> corrupted because that is the point at which you want to stop restoring
> your
> log.
> If you copy the log over to your standby server right after it is backed
> up,
> that's a good thing. Putting a delay of the restoration of the log gives
> you a bit of time to investigate. At the very least, you can restore WITH
> STANDBY to have a look at what the data looked like and then copy that
> data
> back to the primary.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> Hi,
> I've set up two log shipping pairs between three Microsoft SQL Server 2000
> SP3 databases: a primary, a main standby, and an auxiliary standby
> database.
> For the main standby database jobs that create, copy, and restore
> transaction
> log backups run one after another without any delay. Now if the primary
> database fails it may well be because it has become corrupt. According to
> documentation on the Microsoft website and considering my set-up (i.e. no
> delay for loading transaction logs), corrupt data may be carried over into
> the main standby databaseby means of log shipping (could you please
> elaborate
> on how/ in which cases this can happen?) . So in order to deal with a
> situation, when it's not possible to switch over to the main standby
> database, because corrupt data has been carried over into it, I maintain a
> lagged auxiliary standby database, which could be restored up to the
> moment
> of corruption and used instead. The question is, how can I determine this
> moment? Is it the moment a message about the corrupt primary is logged in
> the
> SQL Server error log? Can this moment be determined at all? How would you
> deal with the situation I described?
> Respectfully,
> Oskar
>|||Apparently the problem with this is that the error message may be logged only
some time after the corruption. How long does it take to log the message
after the corruption has taken place?
"Tom Moreau" wrote:
> The error log is your best bet - if SQL Server detected the corruption.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
> Thanks Tom. Though by the word "corruption" here I meant the corruption of
> SQL Server's internal storage structures (for lack of a better name),
> detected by the CHECKDB command, not the application or user negligence.
> The question still remains, how can I determine the moment, just before
> corruption was loaded into the main standby database, after which all the
> remaining transaction log backups are to be discarded?
>
> "Tom Moreau" wrote:
> > Very often, the cause is data corruption is the user or administration.
> > For
> > example, someone *thought* they were in test but they were in prod and
> > deleted or modified data. It's very important to know when the data was
> > corrupted because that is the point at which you want to stop restoring
> > your
> > log.
> >
> > If you copy the log over to your standby server right after it is backed
> > up,
> > that's a good thing. Putting a delay of the restoration of the log gives
> > you a bit of time to investigate. At the very least, you can restore WITH
> > STANDBY to have a look at what the data looked like and then copy that
> > data
> > back to the primary.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> > news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> > Hi,
> > I've set up two log shipping pairs between three Microsoft SQL Server 2000
> > SP3 databases: a primary, a main standby, and an auxiliary standby
> > database.
> > For the main standby database jobs that create, copy, and restore
> > transaction
> > log backups run one after another without any delay. Now if the primary
> > database fails it may well be because it has become corrupt. According to
> > documentation on the Microsoft website and considering my set-up (i.e. no
> > delay for loading transaction logs), corrupt data may be carried over into
> > the main standby databaseby means of log shipping (could you please
> > elaborate
> > on how/ in which cases this can happen?) . So in order to deal with a
> > situation, when it's not possible to switch over to the main standby
> > database, because corrupt data has been carried over into it, I maintain a
> > lagged auxiliary standby database, which could be restored up to the
> > moment
> > of corruption and used instead. The question is, how can I determine this
> > moment? Is it the moment a message about the corrupt primary is logged in
> > the
> > SQL Server error log? Can this moment be determined at all? How would you
> > deal with the situation I described?
> >
> > Respectfully,
> > Oskar
> >
> >
>|||That depends. If SQL Server notices it, it goes into the log right away.
However, I have seen situations where it does not notice it and you find out
the hard way.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:F4DDD609-5D12-4E92-A6A9-6A437EE5C746@.microsoft.com...
Apparently the problem with this is that the error message may be logged
only
some time after the corruption. How long does it take to log the message
after the corruption has taken place?
"Tom Moreau" wrote:
> The error log is your best bet - if SQL Server detected the corruption.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:16E74120-24C8-4C6E-B937-61A32EB727CA@.microsoft.com...
> Thanks Tom. Though by the word "corruption" here I meant the corruption of
> SQL Server's internal storage structures (for lack of a better name),
> detected by the CHECKDB command, not the application or user negligence.
> The question still remains, how can I determine the moment, just before
> corruption was loaded into the main standby database, after which all the
> remaining transaction log backups are to be discarded?
>
> "Tom Moreau" wrote:
> > Very often, the cause is data corruption is the user or administration.
> > For
> > example, someone *thought* they were in test but they were in prod and
> > deleted or modified data. It's very important to know when the data was
> > corrupted because that is the point at which you want to stop restoring
> > your
> > log.
> >
> > If you copy the log over to your standby server right after it is backed
> > up,
> > that's a good thing. Putting a delay of the restoration of the log
> > gives
> > you a bit of time to investigate. At the very least, you can restore
> > WITH
> > STANDBY to have a look at what the data looked like and then copy that
> > data
> > back to the primary.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> > news:B6562D0D-99C4-4291-81DB-A5C67D5DE334@.microsoft.com...
> > Hi,
> > I've set up two log shipping pairs between three Microsoft SQL Server
> > 2000
> > SP3 databases: a primary, a main standby, and an auxiliary standby
> > database.
> > For the main standby database jobs that create, copy, and restore
> > transaction
> > log backups run one after another without any delay. Now if the primary
> > database fails it may well be because it has become corrupt. According
> > to
> > documentation on the Microsoft website and considering my set-up (i.e.
> > no
> > delay for loading transaction logs), corrupt data may be carried over
> > into
> > the main standby databaseby means of log shipping (could you please
> > elaborate
> > on how/ in which cases this can happen?) . So in order to deal with a
> > situation, when it's not possible to switch over to the main standby
> > database, because corrupt data has been carried over into it, I maintain
> > a
> > lagged auxiliary standby database, which could be restored up to the
> > moment
> > of corruption and used instead. The question is, how can I determine
> > this
> > moment? Is it the moment a message about the corrupt primary is logged
> > in
> > the
> > SQL Server error log? Can this moment be determined at all? How would
> > you
> > deal with the situation I described?
> >
> > Respectfully,
> > Oskar
> >
> >
>