November 15, 2010 at 12:26 am
If the log file gets corrupted, is it possible to rectify the corruption and
restore the database to point in time.
Please share your inputs.
M&M
November 15, 2010 at 1:27 am
mohammed moinudheen (11/15/2010)
If the log file gets corrupted, is it possible to rectify the corruption andrestore the database to point in time.
Please share your inputs.
Yep,
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
http://www.sqlskills.com/pastConferences.asp
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 15, 2010 at 1:29 am
This is also good start to find the Corruption
Help, my database is corrupt. Now what?[/url]
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 15, 2010 at 2:08 am
mohammed moinudheen (11/15/2010)
If the log file gets corrupted, is it possible to rectify the corruption andrestore the database to point in time.
The easiest and safest approach is to Restore the Database from backups.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 15, 2010 at 2:17 am
muthukkumaran (11/15/2010)
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Aaah. That link's for people how have stupidly deleted their transaction logs, not for a corrupt log.
Often the best solution for a corrupt log is to restore from backup. Depends how serious the corruption is. Sometimes (if there is no good backup) then an emergency mode repair may be indicated, but the tradeoffs of data loss, downtime and possible further corruption and transactional inconsistency should be carefully considered
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2010 at 2:20 am
I think I confused the question. Here, the transaction log backup itself is corrupt. Is it possible to rectify the corruption in the transaction log and still use the log files backups for point in time recovery.
M&M
November 15, 2010 at 2:50 am
mohammed moinudheen (11/15/2010)
I think I confused the question. Here, the transaction log backup itself is corrupt. Is it possible to rectify the corruption in the transaction log and still use the log files backups for point in time recovery.
Yep,
If you database log file still online/accessible ,then you can take the tail of log and you can restore the PIT.
PIT --works only full/Bulk logged recovery model.
But in bulk logged
If the transaction log does not have any bulk log operation then point in time recovery is possible
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 15, 2010 at 2:56 am
GilaMonster (11/15/2010)
muthukkumaran (11/15/2010)
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/Aaah. That link's for people how have stupidly deleted their transaction logs, not for a corrupt log.
Often the best solution for a corrupt log is to restore from backup. Depends how serious the corruption is. Sometimes (if there is no good backup) then an emergency mode repair may be indicated, but the tradeoffs of data loss, downtime and possible further corruption and transactional inconsistency should be carefully considered
Yep agreed gail.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 15, 2010 at 3:14 am
Yep,
If you database log file still online/accessible ,then you can take the tail of log and you can restore the PIT..[/quote]
But what if the log backup that you already have is showing as corrupt.
Then point in time recovery is not possible right?
M&M
November 15, 2010 at 3:16 am
I messed up the earlier quote option
mohammed moinudheen (11/15/2010)
Yep,If you database log file still online/accessible ,then you can take the tail of log and you can restore the PIT..
[/quote]
But what if the log backup that you already have is showing as corrupt.
Then point in time recovery is not possible right?
M&M
November 15, 2010 at 6:55 am
muthukkumaran (11/15/2010)
If you database log file still online/accessible ,then you can take the tail of log and you can restore the PIT.
Typically if the transaction log is corrupt, you cannot take a tail-log backup. It is sometimes possible to take a full/diff backup in that situation though, even when the tail-log backup fails. Depends what state the database is in and what kind of log corruption (and how bad) you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2010 at 7:04 am
mohammed moinudheen (11/15/2010)
But what if the log backup that you already have is showing as corrupt.Then point in time recovery is not possible right?
If one of your log backups is corrupt and the transaction log is corrupt, then the best restore is to the last good log backup.
If the data loss is too large, it may be possible to rebuild the log, but care should be taken with that option as it is possible to cause transactional and even structural inconsistencies in the database by doing so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2010 at 7:35 am
GilaMonster (11/15/2010)
muthukkumaran (11/15/2010)
If you database log file still online/accessible ,then you can take the tail of log and you can restore the PIT.Typically if the transaction log is corrupt, you cannot take a tail-log backup. It is sometimes possible to take a full/diff backup in that situation though, even when the tail-log backup fails. Depends what state the database is in and what kind of log corruption (and how bad) you have.
Agreed, It depends upon the corruption.
OP asked
But what if the log backup that you already have is showing as corrupt.
Gail i have a question
THe OP can able to restore the corrupted backups using
"WITH CONTINUE_AFTER_ERROR" cammand or not.
If yes , I think the data loss will be the question ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply