0

I've seen a few variations of this question, but no clear cut answers to my situation.

I have an existing log shipping setup, and want to add a secondary server. Here is how I understand what will happen, please correct me if I am wrong.

  1. Restore backup from 12 hours ago to secondary, using no recovery.

  2. Add the secondary database to log shipping which creates the copy and restore job on the secondary and the copy job copies ALL the .trn files from shared backup location to secondary server. (no matter if they are needed or not)

  3. The restore job is able to look at the DB on the secondary and see what the last LSN applied is, then find the appropriate log backup to apply, then apply all subsequent log backups to bring the DB into a consistent state with the primary.

last questions is where I am not sure... Do I need to manually apply all the log backups since the full backup myself, or will log shipping work as I described above and do it automatically?

Improve this question
1
  • Sorry i missed your comment. We are on SQL Server 2016 SP2 CU5
    – DBA Greg14
    Mar 20, 2019 at 14:19

1 Answer 1

Reset to default
0

Yes, you should apply the latest FULL and all the applicable Transaction log backups in sequence after that full, manually on the secondary with no recovery, prior to setting up the log shipping jobs and turning them on (using the wizard with no initialization). When the log shipping wizard is used, it will, as you said, create the backup job on the primary and the copy and restore agent jobs on the secondary which looks for the next LSN to apply after the last one you applied manually when it starts applying the logs it needs for the log shipping process to begin.

Improve this answer
3
  • Thanks for the response! I ran out of time last night and could not apply the transaction logs and left the secondary in the recovery state. i expected to come in today the logs, but i found that the database had fully synced up via log shipping by itself. Copy and Restore jobs completed on their schedules overnight and the DB is in standby / read-only state. I checked the built in log shipping reports in SSMS, and they indicate that log shipping is working correctly with no issues. We are on SQL 2016 SP2 CU5, I'm wondering if there was an enhancement to add this functionality.
    – DBA Greg14
    Mar 20, 2019 at 12:53
  • 1
    It sounds like the set up was already good and working as it should. Maybe it just needed to catch itself up. Glad to hear it's working now!
    – rvsc48
    Mar 20, 2019 at 14:44
  • 1
    A future thought would be to utilize differential backups to accelerate the process. For example, you can create a differential backup (using WITH COPY_ONLY) on the Primary and restore that to the new Secondary database right before running the Restore jobs. Applying that differential to your already restored full helps you "leap frog" over a ton of transaction log backups. Depending on how many transaction log backups would need to be applied by the Secondary, this can make a huge difference. Kendra Little has a great article you can search for if you want more details ("Log Shipping Magic").
    – Micah N
    Mar 20, 2019 at 18:00

Your Answer

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

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