Friends,
Based on information I got during a conversation with MS PSS, I now have the
impression that a LOG backup performed on a DB that has serious CHECKDB
troubles should most likely NOT be restored. (Here, "serious" implies
allow_data_loss as the minimum CHECKDB repair level.)
Our customer made a full backup of a clean happy system. During the course
of the week, part of the database became corrupt. Nobody discovered this
until days after a late-night CHECKDB first reported the serious troubles.
I guessed that perhaps we could restore the full backup, apply the log
backups, and end up "clean". But, "Bob" from MS says that a LOG backup
would most likely have captured the corruption and the corruption would be
present upon LOG restore. I can certainly understand a full backup
capturing corruption, but was surprised that a LOG backup would (or might)
also.
Is "Bob" correct? For now I believe him, but it's nice to get other
opinions. Depending on the "truth", we might need to encourage our
customers to run DBCC CHECKDB far more often, and might need to recommend
automating the monitoring of any problems. Its sounds like any CHECKDB
troubles must be detected and dealt with immediately to minimize the
possibility of actual data loss, since RESTORE might be the only safe option
for recovery.
Any thoughts or links on this topic will be greatly appreciated! Any
thoughts or links on why this corruption might occur would be great too!
Thanks,
James Hunter Ross
Bob is right. A Log backup will capture corruption just like a Full backup
will. Yes, it's best to capture ASAP to avoid lost data. I runn DBCC CheckDB
nightly.
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have
> the impression that a LOG backup performed on a DB that has serious
> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
> implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the
> course of the week, part of the database became corrupt. Nobody
> discovered this until days after a late-night CHECKDB first reported the
> serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log
> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
> would most likely have captured the corruption and the corruption would be
> present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
> also.
> Is "Bob" correct? For now I believe him, but it's nice to get other
> opinions. Depending on the "truth", we might need to encourage our
> customers to run DBCC CHECKDB far more often, and might need to recommend
> automating the monitoring of any problems. Its sounds like any CHECKDB
> troubles must be detected and dealt with immediately to minimize the
> possibility of actual data loss, since RESTORE might be the only safe
> option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any
> thoughts or links on why this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>
|||Will it always be the case? The log file will "only" capture data changes
that SQL is aware of. I'd imagine that if you have a corruption in your
database that comes from e.g. a disk problem, it could happen that this is
only in your database and not in the log. In that case you can restore the
Database backup and the logs and then you'll be ok again.
Regards
Steen
ChrisR wrote:[vbcol=seagreen]
> Bob is right. A Log backup will capture corruption just like a Full
> backup will. Yes, it's best to capture ASAP to avoid lost data. I
> runn DBCC CheckDB nightly.
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
|||I doubt Bob's statement. Imagine you have this entry in the tlog "add a row to this table with these
values for the columns ...". After three hours, the data page where this row ended up is corrupted
because of hw problems. The log record (for the moment in the ldf file) is still clean. You now do a
log backup, and the log record is still clean - now in the tlog backup file. You now restore from
the clean db backup and then restore the clean log record. I fail to see how the corruption would be
introduced!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have the impression that a LOG
> backup performed on a DB that has serious CHECKDB troubles should most likely NOT be restored.
> (Here, "serious" implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the course of the week, part of
> the database became corrupt. Nobody discovered this until days after a late-night CHECKDB first
> reported the serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log backups, and end up
> "clean". But, "Bob" from MS says that a LOG backup would most likely have captured the corruption
> and the corruption would be present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might) also.
> Is "Bob" correct? For now I believe him, but it's nice to get other opinions. Depending on the
> "truth", we might need to encourage our customers to run DBCC CHECKDB far more often, and might
> need to recommend automating the monitoring of any problems. Its sounds like any CHECKDB troubles
> must be detected and dealt with immediately to minimize the possibility of actual data loss, since
> RESTORE might be the only safe option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any thoughts or links on why
> this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>
|||Bob is absolutely correct.
Even though what you say below Tibor is also correct, the odds are that the
corrupt data have been incorporated into the txn log in some way (through a
modification to it, through modifications to corrupt page headers and so on)
after several days of operation.
You may be lucky and there's no corruption propagated in the log, but in the
vast majority of cases I've ever seen, luck does not hold after an interval
of many days.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ON$262ckFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
>to this table with these values for the columns ...". After three hours,
>the data page where this row ended up is corrupted because of hw problems.
>The log record (for the moment in the ldf file) is still clean. You now do
>a log backup, and the log record is still clean - now in the tlog backup
>file. You now restore from the clean db backup and then restore the clean
>log record. I fail to see how the corruption would be introduced!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>
Showing posts with label performed. Show all posts
Showing posts with label performed. Show all posts
Friday, February 24, 2012
LOG Backup in he presence of CHECKDB issues...
Friends,
Based on information I got during a conversation with MS PSS, I now have the
impression that a LOG backup performed on a DB that has serious CHECKDB
troubles should most likely NOT be restored. (Here, "serious" implies
allow_data_loss as the minimum CHECKDB repair level.)
Our customer made a full backup of a clean happy system. During the course
of the week, part of the database became corrupt. Nobody discovered this
until days after a late-night CHECKDB first reported the serious troubles.
I guessed that perhaps we could restore the full backup, apply the log
backups, and end up "clean". But, "Bob" from MS says that a LOG backup
would most likely have captured the corruption and the corruption would be
present upon LOG restore. I can certainly understand a full backup
capturing corruption, but was surprised that a LOG backup would (or might)
also.
Is "Bob" correct? For now I believe him, but it's nice to get other
opinions. Depending on the "truth", we might need to encourage our
customers to run DBCC CHECKDB far more often, and might need to recommend
automating the monitoring of any problems. Its sounds like any CHECKDB
troubles must be detected and dealt with immediately to minimize the
possibility of actual data loss, since RESTORE might be the only safe option
for recovery.
Any thoughts or links on this topic will be greatly appreciated! Any
thoughts or links on why this corruption might occur would be great too!
Thanks,
James Hunter RossBob is right. A Log backup will capture corruption just like a Full backup
will. Yes, it's best to capture ASAP to avoid lost data. I runn DBCC CheckDB
nightly.
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have
> the impression that a LOG backup performed on a DB that has serious
> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
> implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the
> course of the week, part of the database became corrupt. Nobody
> discovered this until days after a late-night CHECKDB first reported the
> serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log
> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
> would most likely have captured the corruption and the corruption would be
> present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
> also.
> Is "Bob" correct? For now I believe him, but it's nice to get other
> opinions. Depending on the "truth", we might need to encourage our
> customers to run DBCC CHECKDB far more often, and might need to recommend
> automating the monitoring of any problems. Its sounds like any CHECKDB
> troubles must be detected and dealt with immediately to minimize the
> possibility of actual data loss, since RESTORE might be the only safe
> option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any
> thoughts or links on why this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Will it always be the case? The log file will "only" capture data changes
that SQL is aware of. I'd imagine that if you have a corruption in your
database that comes from e.g. a disk problem, it could happen that this is
only in your database and not in the log. In that case you can restore the
Database backup and the logs and then you'll be ok again.
Regards
Steen
ChrisR wrote:[vbcol=seagreen]
> Bob is right. A Log backup will capture corruption just like a Full
> backup will. Yes, it's best to capture ASAP to avoid lost data. I
> runn DBCC CheckDB nightly.
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...|||I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
to this table with these
values for the columns ...". After three hours, the data page where this row
ended up is corrupted
because of hw problems. The log record (for the moment in the ldf file) is s
till clean. You now do a
log backup, and the log record is still clean - now in the tlog backup file.
You now restore from
the clean db backup and then restore the clean log record. I fail to see how
the corruption would be
introduced!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have t
he impression that a LOG
> backup performed on a DB that has serious CHECKDB troubles should most lik
ely NOT be restored.
> (Here, "serious" implies allow_data_loss as the minimum CHECKDB repair lev
el.)
> Our customer made a full backup of a clean happy system. During the cours
e of the week, part of
> the database became corrupt. Nobody discovered this until days after a la
te-night CHECKDB first
> reported the serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log bac
kups, and end up
> "clean". But, "Bob" from MS says that a LOG backup would most likely have
captured the corruption
> and the corruption would be present upon LOG restore. I can certainly und
erstand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
also.
> Is "Bob" correct? For now I believe him, but it's nice to get other opini
ons. Depending on the
> "truth", we might need to encourage our customers to run DBCC CHECKDB far
more often, and might
> need to recommend automating the monitoring of any problems. Its sounds l
ike any CHECKDB troubles
> must be detected and dealt with immediately to minimize the possibility of
actual data loss, since
> RESTORE might be the only safe option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any thou
ghts or links on why
> this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Bob is absolutely correct.
Even though what you say below Tibor is also correct, the odds are that the
corrupt data have been incorporated into the txn log in some way (through a
modification to it, through modifications to corrupt page headers and so on)
after several days of operation.
You may be lucky and there's no corruption propagated in the log, but in the
vast majority of cases I've ever seen, luck does not hold after an interval
of many days.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ON$262ckFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
>to this table with these values for the columns ...". After three hours,
>the data page where this row ended up is corrupted because of hw problems.
>The log record (for the moment in the ldf file) is still clean. You now do
>a log backup, and the log record is still clean - now in the tlog backup
>file. You now restore from the clean db backup and then restore the clean
>log record. I fail to see how the corruption would be introduced!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>
Based on information I got during a conversation with MS PSS, I now have the
impression that a LOG backup performed on a DB that has serious CHECKDB
troubles should most likely NOT be restored. (Here, "serious" implies
allow_data_loss as the minimum CHECKDB repair level.)
Our customer made a full backup of a clean happy system. During the course
of the week, part of the database became corrupt. Nobody discovered this
until days after a late-night CHECKDB first reported the serious troubles.
I guessed that perhaps we could restore the full backup, apply the log
backups, and end up "clean". But, "Bob" from MS says that a LOG backup
would most likely have captured the corruption and the corruption would be
present upon LOG restore. I can certainly understand a full backup
capturing corruption, but was surprised that a LOG backup would (or might)
also.
Is "Bob" correct? For now I believe him, but it's nice to get other
opinions. Depending on the "truth", we might need to encourage our
customers to run DBCC CHECKDB far more often, and might need to recommend
automating the monitoring of any problems. Its sounds like any CHECKDB
troubles must be detected and dealt with immediately to minimize the
possibility of actual data loss, since RESTORE might be the only safe option
for recovery.
Any thoughts or links on this topic will be greatly appreciated! Any
thoughts or links on why this corruption might occur would be great too!
Thanks,
James Hunter RossBob is right. A Log backup will capture corruption just like a Full backup
will. Yes, it's best to capture ASAP to avoid lost data. I runn DBCC CheckDB
nightly.
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have
> the impression that a LOG backup performed on a DB that has serious
> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
> implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the
> course of the week, part of the database became corrupt. Nobody
> discovered this until days after a late-night CHECKDB first reported the
> serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log
> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
> would most likely have captured the corruption and the corruption would be
> present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
> also.
> Is "Bob" correct? For now I believe him, but it's nice to get other
> opinions. Depending on the "truth", we might need to encourage our
> customers to run DBCC CHECKDB far more often, and might need to recommend
> automating the monitoring of any problems. Its sounds like any CHECKDB
> troubles must be detected and dealt with immediately to minimize the
> possibility of actual data loss, since RESTORE might be the only safe
> option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any
> thoughts or links on why this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Will it always be the case? The log file will "only" capture data changes
that SQL is aware of. I'd imagine that if you have a corruption in your
database that comes from e.g. a disk problem, it could happen that this is
only in your database and not in the log. In that case you can restore the
Database backup and the logs and then you'll be ok again.
Regards
Steen
ChrisR wrote:[vbcol=seagreen]
> Bob is right. A Log backup will capture corruption just like a Full
> backup will. Yes, it's best to capture ASAP to avoid lost data. I
> runn DBCC CheckDB nightly.
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...|||I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
to this table with these
values for the columns ...". After three hours, the data page where this row
ended up is corrupted
because of hw problems. The log record (for the moment in the ldf file) is s
till clean. You now do a
log backup, and the log record is still clean - now in the tlog backup file.
You now restore from
the clean db backup and then restore the clean log record. I fail to see how
the corruption would be
introduced!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have t
he impression that a LOG
> backup performed on a DB that has serious CHECKDB troubles should most lik
ely NOT be restored.
> (Here, "serious" implies allow_data_loss as the minimum CHECKDB repair lev
el.)
> Our customer made a full backup of a clean happy system. During the cours
e of the week, part of
> the database became corrupt. Nobody discovered this until days after a la
te-night CHECKDB first
> reported the serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log bac
kups, and end up
> "clean". But, "Bob" from MS says that a LOG backup would most likely have
captured the corruption
> and the corruption would be present upon LOG restore. I can certainly und
erstand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
also.
> Is "Bob" correct? For now I believe him, but it's nice to get other opini
ons. Depending on the
> "truth", we might need to encourage our customers to run DBCC CHECKDB far
more often, and might
> need to recommend automating the monitoring of any problems. Its sounds l
ike any CHECKDB troubles
> must be detected and dealt with immediately to minimize the possibility of
actual data loss, since
> RESTORE might be the only safe option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any thou
ghts or links on why
> this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Bob is absolutely correct.
Even though what you say below Tibor is also correct, the odds are that the
corrupt data have been incorporated into the txn log in some way (through a
modification to it, through modifications to corrupt page headers and so on)
after several days of operation.
You may be lucky and there's no corruption propagated in the log, but in the
vast majority of cases I've ever seen, luck does not hold after an interval
of many days.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ON$262ckFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
>to this table with these values for the columns ...". After three hours,
>the data page where this row ended up is corrupted because of hw problems.
>The log record (for the moment in the ldf file) is still clean. You now do
>a log backup, and the log record is still clean - now in the tlog backup
>file. You now restore from the clean db backup and then restore the clean
>log record. I fail to see how the corruption would be introduced!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>
LOG Backup in he presence of CHECKDB issues...
Friends,
Based on information I got during a conversation with MS PSS, I now have the
impression that a LOG backup performed on a DB that has serious CHECKDB
troubles should most likely NOT be restored. (Here, "serious" implies
allow_data_loss as the minimum CHECKDB repair level.)
Our customer made a full backup of a clean happy system. During the course
of the week, part of the database became corrupt. Nobody discovered this
until days after a late-night CHECKDB first reported the serious troubles.
I guessed that perhaps we could restore the full backup, apply the log
backups, and end up "clean". But, "Bob" from MS says that a LOG backup
would most likely have captured the corruption and the corruption would be
present upon LOG restore. I can certainly understand a full backup
capturing corruption, but was surprised that a LOG backup would (or might)
also.
Is "Bob" correct? For now I believe him, but it's nice to get other
opinions. Depending on the "truth", we might need to encourage our
customers to run DBCC CHECKDB far more often, and might need to recommend
automating the monitoring of any problems. Its sounds like any CHECKDB
troubles must be detected and dealt with immediately to minimize the
possibility of actual data loss, since RESTORE might be the only safe option
for recovery.
Any thoughts or links on this topic will be greatly appreciated! Any
thoughts or links on why this corruption might occur would be great too!
Thanks,
James Hunter RossBob is right. A Log backup will capture corruption just like a Full backup
will. Yes, it's best to capture ASAP to avoid lost data. I runn DBCC CheckDB
nightly.
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have
> the impression that a LOG backup performed on a DB that has serious
> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
> implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the
> course of the week, part of the database became corrupt. Nobody
> discovered this until days after a late-night CHECKDB first reported the
> serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log
> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
> would most likely have captured the corruption and the corruption would be
> present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
> also.
> Is "Bob" correct? For now I believe him, but it's nice to get other
> opinions. Depending on the "truth", we might need to encourage our
> customers to run DBCC CHECKDB far more often, and might need to recommend
> automating the monitoring of any problems. Its sounds like any CHECKDB
> troubles must be detected and dealt with immediately to minimize the
> possibility of actual data loss, since RESTORE might be the only safe
> option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any
> thoughts or links on why this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Will it always be the case? The log file will "only" capture data changes
that SQL is aware of. I'd imagine that if you have a corruption in your
database that comes from e.g. a disk problem, it could happen that this is
only in your database and not in the log. In that case you can restore the
Database backup and the logs and then you'll be ok again.
Regards
Steen
ChrisR wrote:
> Bob is right. A Log backup will capture corruption just like a Full
> backup will. Yes, it's best to capture ASAP to avoid lost data. I
> runn DBCC CheckDB nightly.
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> Based on information I got during a conversation with MS PSS, I now
>> have the impression that a LOG backup performed on a DB that has
>> serious CHECKDB troubles should most likely NOT be restored. (Here,
>> "serious" implies allow_data_loss as the minimum CHECKDB repair
>> level.) Our customer made a full backup of a clean happy system. During
>> the
>> course of the week, part of the database became corrupt. Nobody
>> discovered this until days after a late-night CHECKDB first reported
>> the serious troubles.
>> I guessed that perhaps we could restore the full backup, apply the
>> log backups, and end up "clean". But, "Bob" from MS says that a LOG
>> backup would most likely have captured the corruption and the
>> corruption would be present upon LOG restore. I can certainly
>> understand a full backup capturing corruption, but was surprised
>> that a LOG backup would (or might) also.
>> Is "Bob" correct? For now I believe him, but it's nice to get other
>> opinions. Depending on the "truth", we might need to encourage our
>> customers to run DBCC CHECKDB far more often, and might need to
>> recommend automating the monitoring of any problems. Its sounds
>> like any CHECKDB troubles must be detected and dealt with
>> immediately to minimize the possibility of actual data loss, since
>> RESTORE might be the only safe option for recovery.
>> Any thoughts or links on this topic will be greatly appreciated! Any
>> thoughts or links on why this corruption might occur would be great
>> too! Thanks,
>> James Hunter Ross|||I doubt Bob's statement. Imagine you have this entry in the tlog "add a row to this table with these
values for the columns ...". After three hours, the data page where this row ended up is corrupted
because of hw problems. The log record (for the moment in the ldf file) is still clean. You now do a
log backup, and the log record is still clean - now in the tlog backup file. You now restore from
the clean db backup and then restore the clean log record. I fail to see how the corruption would be
introduced!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have the impression that a LOG
> backup performed on a DB that has serious CHECKDB troubles should most likely NOT be restored.
> (Here, "serious" implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the course of the week, part of
> the database became corrupt. Nobody discovered this until days after a late-night CHECKDB first
> reported the serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log backups, and end up
> "clean". But, "Bob" from MS says that a LOG backup would most likely have captured the corruption
> and the corruption would be present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might) also.
> Is "Bob" correct? For now I believe him, but it's nice to get other opinions. Depending on the
> "truth", we might need to encourage our customers to run DBCC CHECKDB far more often, and might
> need to recommend automating the monitoring of any problems. Its sounds like any CHECKDB troubles
> must be detected and dealt with immediately to minimize the possibility of actual data loss, since
> RESTORE might be the only safe option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any thoughts or links on why
> this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Bob is absolutely correct.
Even though what you say below Tibor is also correct, the odds are that the
corrupt data have been incorporated into the txn log in some way (through a
modification to it, through modifications to corrupt page headers and so on)
after several days of operation.
You may be lucky and there's no corruption propagated in the log, but in the
vast majority of cases I've ever seen, luck does not hold after an interval
of many days.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ON$262ckFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
>to this table with these values for the columns ...". After three hours,
>the data page where this row ended up is corrupted because of hw problems.
>The log record (for the moment in the ldf file) is still clean. You now do
>a log backup, and the log record is still clean - now in the tlog backup
>file. You now restore from the clean db backup and then restore the clean
>log record. I fail to see how the corruption would be introduced!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> Based on information I got during a conversation with MS PSS, I now have
>> the impression that a LOG backup performed on a DB that has serious
>> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
>> implies allow_data_loss as the minimum CHECKDB repair level.)
>> Our customer made a full backup of a clean happy system. During the
>> course of the week, part of the database became corrupt. Nobody
>> discovered this until days after a late-night CHECKDB first reported the
>> serious troubles.
>> I guessed that perhaps we could restore the full backup, apply the log
>> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
>> would most likely have captured the corruption and the corruption would
>> be present upon LOG restore. I can certainly understand a full backup
>> capturing corruption, but was surprised that a LOG backup would (or
>> might) also.
>> Is "Bob" correct? For now I believe him, but it's nice to get other
>> opinions. Depending on the "truth", we might need to encourage our
>> customers to run DBCC CHECKDB far more often, and might need to recommend
>> automating the monitoring of any problems. Its sounds like any CHECKDB
>> troubles must be detected and dealt with immediately to minimize the
>> possibility of actual data loss, since RESTORE might be the only safe
>> option for recovery.
>> Any thoughts or links on this topic will be greatly appreciated! Any
>> thoughts or links on why this corruption might occur would be great too!
>> Thanks,
>> James Hunter Ross
>>
>|||Thank you all! It's sure nice to hear from a SQL Server development team
member too!
We will certainly modify our "best practices" recommendations to include
DBCC CHECKDB at least nightly, with a requisite monitoring of the results.
The corruption is rare, but "rare" is the whole reason we have a recovery
plan recommendation for our customers. Thanks again!
James
Based on information I got during a conversation with MS PSS, I now have the
impression that a LOG backup performed on a DB that has serious CHECKDB
troubles should most likely NOT be restored. (Here, "serious" implies
allow_data_loss as the minimum CHECKDB repair level.)
Our customer made a full backup of a clean happy system. During the course
of the week, part of the database became corrupt. Nobody discovered this
until days after a late-night CHECKDB first reported the serious troubles.
I guessed that perhaps we could restore the full backup, apply the log
backups, and end up "clean". But, "Bob" from MS says that a LOG backup
would most likely have captured the corruption and the corruption would be
present upon LOG restore. I can certainly understand a full backup
capturing corruption, but was surprised that a LOG backup would (or might)
also.
Is "Bob" correct? For now I believe him, but it's nice to get other
opinions. Depending on the "truth", we might need to encourage our
customers to run DBCC CHECKDB far more often, and might need to recommend
automating the monitoring of any problems. Its sounds like any CHECKDB
troubles must be detected and dealt with immediately to minimize the
possibility of actual data loss, since RESTORE might be the only safe option
for recovery.
Any thoughts or links on this topic will be greatly appreciated! Any
thoughts or links on why this corruption might occur would be great too!
Thanks,
James Hunter RossBob is right. A Log backup will capture corruption just like a Full backup
will. Yes, it's best to capture ASAP to avoid lost data. I runn DBCC CheckDB
nightly.
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have
> the impression that a LOG backup performed on a DB that has serious
> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
> implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the
> course of the week, part of the database became corrupt. Nobody
> discovered this until days after a late-night CHECKDB first reported the
> serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log
> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
> would most likely have captured the corruption and the corruption would be
> present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
> also.
> Is "Bob" correct? For now I believe him, but it's nice to get other
> opinions. Depending on the "truth", we might need to encourage our
> customers to run DBCC CHECKDB far more often, and might need to recommend
> automating the monitoring of any problems. Its sounds like any CHECKDB
> troubles must be detected and dealt with immediately to minimize the
> possibility of actual data loss, since RESTORE might be the only safe
> option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any
> thoughts or links on why this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Will it always be the case? The log file will "only" capture data changes
that SQL is aware of. I'd imagine that if you have a corruption in your
database that comes from e.g. a disk problem, it could happen that this is
only in your database and not in the log. In that case you can restore the
Database backup and the logs and then you'll be ok again.
Regards
Steen
ChrisR wrote:
> Bob is right. A Log backup will capture corruption just like a Full
> backup will. Yes, it's best to capture ASAP to avoid lost data. I
> runn DBCC CheckDB nightly.
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> Based on information I got during a conversation with MS PSS, I now
>> have the impression that a LOG backup performed on a DB that has
>> serious CHECKDB troubles should most likely NOT be restored. (Here,
>> "serious" implies allow_data_loss as the minimum CHECKDB repair
>> level.) Our customer made a full backup of a clean happy system. During
>> the
>> course of the week, part of the database became corrupt. Nobody
>> discovered this until days after a late-night CHECKDB first reported
>> the serious troubles.
>> I guessed that perhaps we could restore the full backup, apply the
>> log backups, and end up "clean". But, "Bob" from MS says that a LOG
>> backup would most likely have captured the corruption and the
>> corruption would be present upon LOG restore. I can certainly
>> understand a full backup capturing corruption, but was surprised
>> that a LOG backup would (or might) also.
>> Is "Bob" correct? For now I believe him, but it's nice to get other
>> opinions. Depending on the "truth", we might need to encourage our
>> customers to run DBCC CHECKDB far more often, and might need to
>> recommend automating the monitoring of any problems. Its sounds
>> like any CHECKDB troubles must be detected and dealt with
>> immediately to minimize the possibility of actual data loss, since
>> RESTORE might be the only safe option for recovery.
>> Any thoughts or links on this topic will be greatly appreciated! Any
>> thoughts or links on why this corruption might occur would be great
>> too! Thanks,
>> James Hunter Ross|||I doubt Bob's statement. Imagine you have this entry in the tlog "add a row to this table with these
values for the columns ...". After three hours, the data page where this row ended up is corrupted
because of hw problems. The log record (for the moment in the ldf file) is still clean. You now do a
log backup, and the log record is still clean - now in the tlog backup file. You now restore from
the clean db backup and then restore the clean log record. I fail to see how the corruption would be
introduced!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have the impression that a LOG
> backup performed on a DB that has serious CHECKDB troubles should most likely NOT be restored.
> (Here, "serious" implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the course of the week, part of
> the database became corrupt. Nobody discovered this until days after a late-night CHECKDB first
> reported the serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log backups, and end up
> "clean". But, "Bob" from MS says that a LOG backup would most likely have captured the corruption
> and the corruption would be present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might) also.
> Is "Bob" correct? For now I believe him, but it's nice to get other opinions. Depending on the
> "truth", we might need to encourage our customers to run DBCC CHECKDB far more often, and might
> need to recommend automating the monitoring of any problems. Its sounds like any CHECKDB troubles
> must be detected and dealt with immediately to minimize the possibility of actual data loss, since
> RESTORE might be the only safe option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any thoughts or links on why
> this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Bob is absolutely correct.
Even though what you say below Tibor is also correct, the odds are that the
corrupt data have been incorporated into the txn log in some way (through a
modification to it, through modifications to corrupt page headers and so on)
after several days of operation.
You may be lucky and there's no corruption propagated in the log, but in the
vast majority of cases I've ever seen, luck does not hold after an interval
of many days.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ON$262ckFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
>to this table with these values for the columns ...". After three hours,
>the data page where this row ended up is corrupted because of hw problems.
>The log record (for the moment in the ldf file) is still clean. You now do
>a log backup, and the log record is still clean - now in the tlog backup
>file. You now restore from the clean db backup and then restore the clean
>log record. I fail to see how the corruption would be introduced!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> Based on information I got during a conversation with MS PSS, I now have
>> the impression that a LOG backup performed on a DB that has serious
>> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
>> implies allow_data_loss as the minimum CHECKDB repair level.)
>> Our customer made a full backup of a clean happy system. During the
>> course of the week, part of the database became corrupt. Nobody
>> discovered this until days after a late-night CHECKDB first reported the
>> serious troubles.
>> I guessed that perhaps we could restore the full backup, apply the log
>> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
>> would most likely have captured the corruption and the corruption would
>> be present upon LOG restore. I can certainly understand a full backup
>> capturing corruption, but was surprised that a LOG backup would (or
>> might) also.
>> Is "Bob" correct? For now I believe him, but it's nice to get other
>> opinions. Depending on the "truth", we might need to encourage our
>> customers to run DBCC CHECKDB far more often, and might need to recommend
>> automating the monitoring of any problems. Its sounds like any CHECKDB
>> troubles must be detected and dealt with immediately to minimize the
>> possibility of actual data loss, since RESTORE might be the only safe
>> option for recovery.
>> Any thoughts or links on this topic will be greatly appreciated! Any
>> thoughts or links on why this corruption might occur would be great too!
>> Thanks,
>> James Hunter Ross
>>
>|||Thank you all! It's sure nice to hear from a SQL Server development team
member too!
We will certainly modify our "best practices" recommendations to include
DBCC CHECKDB at least nightly, with a requisite monitoring of the results.
The corruption is rare, but "rare" is the whole reason we have a recovery
plan recommendation for our customers. Thanks again!
James
Log all performed SQL Statements
Hi there,
I have a little problem concerning the replication of a SQL Server database. The situation is as follows:
We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.
The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.
Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.
Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?
Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.
So long
TheSentinel85
P.S. I am using MSDE / SQL Server 2000
Transactional replication is just design for this case.It will replay every command at Server B that already issued at Server A.
It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.
Also SQL Server 2000 and MSDE already support it.
Thanks,
Zuomin
|||
Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.
Do you have any good online resource where I can read about replication of SQL Server?
Thanks,
TheSentinel85
|||Sure. Here are some:Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx
Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx
Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.
Thanks,
Zuomin
|||
I have now tried to set up the Replication...again.
But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.
Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.
Is there anything I need to take special care of when setting up the replication?
Thanks,
TheSentinel85
|||Sync replication:Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.
Option 2:
Set up SQL Agent Job do the sync.
Thanks,
Zuomin
|||
Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.
And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?
|||Thanks to you both.
@.ZUOMIN:
ok I will try it manually.
@.JRPM:
Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?
How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.
EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(
So long
|||You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.
|||Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:
http://technet.microsoft.com/en-us/library/ms151756.aspx
Buck Woody|||Hi everyone,
sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.
Hopefully we are going to test the whole thing in the next days...
I will let you know what the results are....
Thank you all so far for your suggestions...
So long
TheSentinel85
Log all performed SQL Statements
Hi there,
I have a little problem concerning the replication of a SQL Server database. The situation is as follows:
We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.
The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.
Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.
Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?
Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.
So long
TheSentinel85
P.S. I am using MSDE / SQL Server 2000
Transactional replication is just design for this case.It will replay every command at Server B that already issued at Server A.
It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.
Also SQL Server 2000 and MSDE already support it.
Thanks,
Zuomin
|||
Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.
Do you have any good online resource where I can read about replication of SQL Server?
Thanks,
TheSentinel85
|||Sure. Here are some:Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx
Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx
Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.
Thanks,
Zuomin
|||
I have now tried to set up the Replication...again.
But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.
Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.
Is there anything I need to take special care of when setting up the replication?
Thanks,
TheSentinel85
|||Sync replication:Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.
Option 2:
Set up SQL Agent Job do the sync.
Thanks,
Zuomin
|||
Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.
And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?
|||Thanks to you both.
@.ZUOMIN:
ok I will try it manually.
@.JRPM:
Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?
How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.
EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(
So long
|||You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.
|||Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:
http://technet.microsoft.com/en-us/library/ms151756.aspx
Buck Woody|||Hi everyone,
sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.
Hopefully we are going to test the whole thing in the next days...
I will let you know what the results are....
Thank you all so far for your suggestions...
So long
TheSentinel85
Log all performed SQL Statements
Hi there,
I have a little problem concerning the replication of a SQL Server database. The situation is as follows:
We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.
The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.
Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.
Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?
Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.
So long
TheSentinel85
P.S. I am using MSDE / SQL Server 2000
Transactional replication is just design for this case.It will replay every command at Server B that already issued at Server A.
It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.
Also SQL Server 2000 and MSDE already support it.
Thanks,
Zuomin
|||
Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.
Do you have any good online resource where I can read about replication of SQL Server?
Thanks,
TheSentinel85
|||Sure. Here are some:Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx
Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx
Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.
Thanks,
Zuomin
|||
I have now tried to set up the Replication...again.
But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.
Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.
Is there anything I need to take special care of when setting up the replication?
Thanks,
TheSentinel85
|||Sync replication:Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.
Option 2:
Set up SQL Agent Job do the sync.
Thanks,
Zuomin
|||
Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.
And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?
|||Thanks to you both.
@.ZUOMIN:
ok I will try it manually.
@.JRPM:
Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?
How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.
EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(
So long
|||You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.
|||Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:
http://technet.microsoft.com/en-us/library/ms151756.aspx
Buck Woody|||Hi everyone,
sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.
Hopefully we are going to test the whole thing in the next days...
I will let you know what the results are....
Thank you all so far for your suggestions...
So long
TheSentinel85
Subscribe to:
Posts (Atom)