sql-serverlog-shipping

Log shipping plan creating readonly standby


I created a maintenance plan for log shipping from main server to secondary server in standby mode. It is creating the standby database in read-only mode. When I change the database to read-write by running restore with recovery, the log shipping restore job, fails. I want to have the secondary server in full access as it is used as fail over. I have previously created plans on the same server, using same procedure, without any issues.


Solution

  • Once you bring online a secondary server, the jobs (backup on primary and copy/restore on secondary) can't be successful - it s by design. You need to disable them. This is because primary now in Restoring state, and secondary is a new primary.

    Read below useful links (third is with images), it explains well how it works, and the steps to fail-over and fail-back.

    https://msdn.microsoft.com/en-us/library/ms191233.aspx

    https://msdn.microsoft.com/en-us/library/ms178117.aspx

    http://dbasqlserver13.blogspot.com.au/2013/12/failover-and-failback-in-logshipping.html