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