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.
Best Answer
Well, kind of. It would probably look a bit more like this:
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).
This isn't a yes or no question. You should keep all backups according to your data retention policy.
Hope this helps!