0

I have a database that is currently shipping log files to a standby server.

I ship the log from the primary to the standby every 5 minutes

Every 15 minutes, a restore Agent Job runs on the standby to import the transactions into the standby database.

I want to be sure I understand how to bring the standby server online in case the primary fails BEFORE I am in that scenario. Everything I have been reading online tells me that I simply need to run restore database }}dbname{{ with recovery to accomplish that.

Questions I have are:

  • 1) Is that the correct code to run?

  • 2) Does that automatically restore any transaction logs that have shipped since the last restore Agent Job was ran?

  • 3) Once the restore Agent Job runs, do I need to keep the .trn files?

I have failed to locate any resources that fully explain the actual switch over process. Lots out there on how to set up log shipping though.

My ultimate goal is to get to a situation where I am doing a full backup each night, shipping it to the standby server and restoring it, deleting all the .trn files prior to the full back up, then do log shipping each 5 minutes, restoring them at 15 minute intervals.

Improve this question

1 Answer 1

Reset to default
4

1) Is that the correct code to run?

Well, kind of. It would probably look a bit more like this:

RESTORE DATABASE [YourMom] WITH RECOVERY

2) Does that automatically restore any transaction logs that have shipped since the last restore Agent Job was ran?

No, it's your job to make sure that Log Shipping is caught up, and if possible (if the primary is still available), take a Tail of the Log (sounds like a bar, I know) backup, which will grab any remaining transactions, and put the database on the primary in a restoring state (will cut off any current users). The TOL backup is a usually a manual step, unless you have third party tools (like Quest LiteSpeed to help).

3) Once the restore Agent Job runs, do I need to keep the .trn files?

This isn't a yes or no question. You should keep all backups according to your data retention policy.

Hope this helps!

Improve this answer
2
  • Perfect answer. Thanks! I am also watching Kendra's video on the Brent Ozar site about log shipping. (I completely forgot I had access to those videos! DOH!!)
    – Keith Clark
    Commented Jul 28, 2017 at 20:55
  • @KeithClark hahaha, yeah, it's sort of like how a lot of people forget they have a gym membership. Glad to help, and enjoy the videos!
    – Erik Reasonable Rates Darling
    Commented Jul 28, 2017 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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