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 :-))
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.
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– DBAuserJun 19, 2012 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. Jun 19, 2012 at 16:56
You can create a full backup of the database in a trn file, using right-click Database->tasks->backup.
You should save that .trn file where your logshipping is saved to.
Then manually restore that on the destination DB, remembering to leave the DB in either a standyby\readonly or restoring state.
The log shipping restore job will then start failing as it will be trying to restore the database to a point prior to where the DB is. So to move that process so it works on the correct file, update the entry in the table
msdb..log_shipping_monitor_secondary for your shipping to the file that you just restored. Eg:
update msdb..log_shipping_monitor_secondary set last_restored_file = 'filename' where secondary_server = 'thisserver' and primary_server = '' ...
The job should then run correctly.
I had the same issue, see Resume log shipping from a specific transaction