Log Shipping multiple times | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log Shipping multiple times

Has anyone configured log shipping to work in the following scenario: Log ship from the Primary DB to the Secondary DB, then configure log shipping from that Secondary DB to another DB? I realise that the secondary DB sits in a warm standby mode therefore will not allow you to backup the logs, but if you could modify the restore job on the secondary to firstly restore the logs from the primary, then change the state of the db, possibly with the RESTORE DATABASE MyDBName WITH RECOVERY command, then backup the logs and return the db to the warm standby state? The third server would then be able to pull the logs from the secondary db and restore to the third db. The logic seems sort of ok, except I’m not sure how to turn change the secondary db back into the warm standby state? Any ideas or comments would be really appreciated!

I have actually found a neater way of doing this now which involves leaving the secondary DB in the warm standby state. Create the log shipping plan on the secondary db to ship to the third db. This will go through aslong as you select to restore from a recent backup, rather than backup now. Once the plan is in place, you can select the source of the transaction log copy by altering the table msdb.log_shipping_plans on the third db. Point that towards the same place where the logs are pulled from the first db and hey presto – it works.