Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

If a write on log shipping transaction backup gets corrupted on one of the transaction file due to network or media issue, and hence the log shipping chain gets broken, is there any other way to restore the database to the point in time ? I understand one could do full backup or diff backups but to restore to point in time, how can we get the consecutive tran logs to be restored? Any pointers on this will be beneficial. Thanks (Even if the solution is to contact Microsoft or only God can help you, I will buy that :-))

share|improve this question

1 Answer

up vote 1 down vote accepted

If you are saying your actual backup file is corrupted (the .trn file) but that the backup was successful from SQL Server's perspective, and the source DB is still online, I would take a full backup as soon as possible so you don't lose any data.

You will have to rebuild your log shipping if you've got a corrupt .trn, but your main concern should be having the ability to restore your source data at all if you've got a bad .trn in your chain.

share|improve this answer
Thanks Eric. I am looking for a upto-the-minute recovery. For Example: Full backup at 1:00am, Diff backup every 2 hours, Tran backups every minute 6:00am, a diff backup is done. 6:01AM, the trn log file was not processed 100%. we have an incomplete file 6:02AM, the trn log files are ok and so on. Around 6:30AM, when doing a full backup, the media fails, you are unable to do a full/diff backup. How would you restore the data from 6:01AM to 6:45AM because at 6:01AM the tran file is not complete and hence you cannot proceed with the rest of tran files. Any idea? Thanks for your time and help – DBAuser Jun 19 '12 at 15:13
You can restore up to the last transaction of the .trn before the corrupted one. There are forensics tools you can acquire that would allow you to extract subsequent trans in the later non-corrupt files, but even this is a data integrity issue since you cannot know what is missing in the corrupted backup. Take a full and start a new chain is my advice. – Eric Higgins Jun 19 '12 at 16:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.