Occasionally, needs arise to modify the recovery model on the primary database involved in Log Shipping. When this happens, a gap in the LSNs is created. The only way to recover from this (once the DB is back in full recovery) is to produce a new full backup and restore that over the existing secondary. Subsequent TRN backups should be restored, as expected. Since most of our databases are still hosted at Aldera, I’d like someone who can help me building a job that focuses on the secondary database side of things – obtaining the latest full backup in a directory and restoring it. This job should be deployable per-database (similar to how the secondary restore jobs are setup).Thank you
I don't know why you might be changing the Recovery Model (and, yes, there are some good reasons) but if you're only changing the Recovery Model to BULK LOGGED, then you don't have to go through all of that because Log Shipping works in both the FULL and BULK LOGGED recovery model.
I agree with Jeff Moden here. First why are you having to change the recovery model. That is usually not something regularly performed. Sounds like maybe that should be researched first. Second, to answer your actual question, would a full be placed into a specific directory with a unique name and then you want a restore job triggered? If so a simple SSIS or SQL Agent job with a file watcher task could accomplish that. If the condition exist, kick off the restore job. Not that hard to accomplish but from what you described, it seems there is a bigger issue with what is causing the change of the recovery model and back. Is it simply to reduce tlog growth?