August 15, 2014 at 5:11 am
I realize that log shipping is for failover, however what happens if we would like a point in time restore?
Say someone deletes all the data in a table and log shipping replicates before it is caught, how would you handle this?
Log shipping has been going for a while so the backup there is old, however we regularly backup the database. We cannot do a log backup as it will cause problems for the log shipping.
There has been talk about doing away with log shipping and starting a point in time backup process, but this means no hot failover.
Any tips?
August 15, 2014 at 5:21 am
Take your last full backup, copy over all the logs that ran since that full backup (from the log shipping secondary), including one log backup that occurred after the data was deleted
Restore full somewhere else then restore all the logs, including the last one WITH STOPAT and specify a time just before the data was deleted.
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
August 15, 2014 at 5:24 am
Thank you! I had wondered if that was possible, but since the log shipping was not directly related to the backup, I did not know if that would work.
August 15, 2014 at 10:09 am
They're just log backups. There's nothing different about log backups used with log shipping than log backups you'd get from maintenance plans
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
August 15, 2014 at 10:47 am
Thanks again. I gave it a try and it worked well. Once I deciphered what MS was saying about it in the KB article.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply