Log shipping to SSRS

I like simplicity, and job schedules can add a ton of it. I set up one job on the primary server to backup logs, and one job on the secondary server to restore them from the primary share.

Both schedules were set to – every 15 minutes between 18:00 and 08:00 – so no overhead during the working day.

Incidentally, so I would never have to manually fix log shipping of a 2 TB database, I set up a 01:00 [full backup] job on the primary …

Which on completion would start a [full restore] job on the secondary (to “standby”)…

The first step of the [full backup] job was to check log shipping latency, and only proceed if it were over 2 hours.

Normally log shipping would run all night and the secondary server would be updated to 08:00. If there were an issue then the [full backup] / [full restore] jobs would “fix” log shipping and again the secondary would be updated to 08:00. If there were a more fundamental failure then the [full backup] / [full restore] jobs alone, would update the secondary to 01:00.

The final touches were to set the [full restore] job to notify me if it were ever executed, and to remove all notifications from the [log restore] job 🙂