May 13, 2008 at 6:51 am
Hi All,
Recently i have got a problem with one database that few rows in a table got accidently deleted.
I tried to rollback the records using SQLLogrescue but failed because by mistake i took one full backup of the database after correction. The full backup available before the deletion is only contains data till 14th of november, after that i have only log backups available for the database.
The log back up set contains log generated on each day after the full backup date and it contains nearly 175 log file set. I tried to restore the database from the fullbackup with the norecovery option then to apply the log backup till date with recovery (i dont care if it is only recover data one day before the error).
But this process gave me an error becuase one transaction from the log was not recoverable. This error log is the coming in the 4th file of the log backup set. I dont know what to do to restore my database at this stage. Please find the below statement which i applied. My SQL Server is on Ver 2000 (SP4).
Restore Database dbcrmappln
from disk='c:\backup\dbcrmappln_full_20081114.bak'
with
move 'dbcrmappln_data' to 'c:\dbcrmappln\dbcrmappln_data.mdf',
move 'dbcrmappln_log' to 'c:\dbcrmappln\dbcrmappln_log.ldf',
replace,norecovery
Restore log dbcrmappln
from disk='c:\backup\dbcrmappln_log.trn'
with recovery
The error came after the execution is like, 'Can't Redo transaction ID... (LSN 145679892).. Restore terminated abnormally'
I also tried with 'Continue_After_Error' option after installing a sql 2005 std edition on another machine , but failed to restore.
I know it is purely a problem of maintaining proper backup strategy, but i was not aware what was going on this database ( this was maintained by anothr person).
Please give me a solution to restore my database by ignoring the error log in the backupset.
Regards,
Saji Krishnan
May 13, 2008 at 7:21 am
Let me make sure I understand your issue, you have a full db backup and a single file with 175 log backups since the full backup. What your code is doing, if I understand the scenario, is attempting to restore the log using the 175th log backup set in the log backup file and you need to restore ALL the log backups in order specifying NoRecovery on all but the last one and adding "With File = 1" to specify the backup set you want to restore. So your restore process needs to be like this:
[font="Courier New"]RESTORE DATABASE dbcrmappln FROM DISK='c:\backup\dbcrmappln_full_20081114.bak'
WITH move 'dbcrmappln_data' TO 'c:\dbcrmappln\dbcrmappln_data.mdf',
move 'dbcrmappln_log' TO 'c:\dbcrmappln\dbcrmappln_log.ldf',
REPLACE,
norecovery
RESTORE LOG dbcrmappln FROM DISK='c:\backup\dbcrmappln_log.trn'
WITH FILE = 1,
norecovery
RESTORE LOG dbcrmappln FROM DISK='c:\backup\dbcrmappln_log.trn'
WITH FILE = 2,
norecovery
...
RESTORE LOG dbcrmappln FROM DISK='c:\backup\dbcrmappln_log.trn'
WITH FILE = 175,
recovery
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 15, 2008 at 3:19 pm
if i have understand u this the way things r:
u have one full backup, and a Series of log backups and one of them is Corrupted.
if so then u cant restore any log file after the corrupted one.
..>>..
MobashA
May 16, 2008 at 2:15 am
Hi,
Yes i tried this, but when it is trying to restore the File No: 4 it shows that one transaction in that log cant redo.So it is terminating the process at this point. Now i need to skip the incorrect transaction in the 4th log.
May 16, 2008 at 2:56 am
Yes, you said it, r u sure there is no way to overcome this problem i mean to ignore the corructed transaction.
May 16, 2008 at 6:06 am
have u search on the net for log recovery, u can extrace the transaction and apply them manauly,
if it is possible to do so.
..>>..
MobashA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply