locked
SQL Log Shipping (Using Server 2012R2) RRS feed

  • Question

  • Hii,

    Currently using log shipping without using monitoring server.Set primary server database as log shipping primary database.

    Data transfer from Primary to secondary successfully.

    I have doubt ,if my primary server goes DOWN and my application should use standby server database.But secondary server is in Recovery mode.How to remove recovery mode?.I tried to remove that recovery mode by Restoring secondary database.If my primary server goes UP then how can i restore secondary Database on Primary? and start log shipping on primary server.


    • Edited by SHARVARI_ Saturday, January 28, 2017 7:29 AM
    Saturday, January 28, 2017 7:28 AM

All replies

  • Hello SHARVARI_

    If your current primary (P1) log shipping server goes down and it exceeds your RTO, then you may want to failover to your secondary (S1) log shipping server and direct your applications (manuall) to that server. You can change the DNS records or your connection strings, it depends on your strategy.

    To bring the databases online on the secondary (S1) log shipping server, first ensure that you restored all available transaction log files, including tail of the log backups when possible. Then simple restore your databases using the following command

    RESTORE DATABASE <db_name> WITH RECOVERY;

    When your then-primary (P1) server goes up again, you'll have to perform database backups on the now-primary (S1) server and restore these backups on the then-primary (P1) server (original primary server) and configure the log shipping from the now-primary (S1) server to the then-primary (P1) server. If it's needed, you can perform another manual failback to your original primary (P1) server using the same method.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Saturday, January 28, 2017 8:49 AM
  • Refer to the following link for how to do role reversal using Microsoft log shipping wizards:

    https://msdn.microsoft.com/en-us/library/ms191233.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/ms178117.aspx?f=255&MSPPError=-2147217396

    Note that these do require you to run the restore database DatabaseName (as Ekrem points out) with recovery after applying the tail of the log on the database.

    Saturday, January 28, 2017 1:14 PM