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 :-))
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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
The job should then run correctly.
I had the same issue, see Resume log shipping from a specific transaction