question

nansa avatar image
nansa asked

Help with automation script for copy and restore

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
restorecopy
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
That sounds like some pretty specific contracting work, not a straight forward question to get answered on a forum.
2 Likes 2 ·
Jeff Moden avatar image
Jeff Moden answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BradleySQL avatar image
BradleySQL answered
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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.