none
SQL Server 2008 R2 Log Shipping RRS feed

  • Question

  • HI All,

          WE have configured Log Shipping on our SQL Server 2008 R2 server from PR to DR.  Now we are planning to Mock DR Drill. For this our app team are considering following Scenario. 

    1) They want to do some read write operations on the DR Slave server to test app functionality and DR Data. For this do we need to Break log shipping  and after app team does their testing We have to re-enable the log shipping.  Please suggest us follwoing

    1) steps to cancel the Log shipping in proper way

    2) if we shutdown DR server and take a file system backup and  start SQL Server on slave server and stop log shipping jobs and enable databases to recovery mode and after app team completes their activity again shutdown the DR instance and restore earlier taken cold backup of file system and  bring up the instance and enabling log shipping job would work? Please suggest.

    or do we have any better solution in this case?

    Regards,

    Varun

    Sunday, November 29, 2015 2:33 PM

Answers

  • 1. I suggest you are asking step to break logshipping. If so please read below link

    Remove Logshipping

    2. I would not suggest taking file system backup. Before breaking logshipping make sure that whatever log backup is generated is restored on Secondary(dont call it slave) if not manually copy and restore it so as to avoid as less data loss as possible. You can use below queries

    select * from msdb.dbo.log_shipping_monitor_primary--on primary
    go
    select * from msdb.dbo.log_shipping_monitor_secondary--on secondary

    to see what log backups you have and what is not restored.

    After your testing is done and since you only did read operation on Secondary. Take full backup of primary and restore on secondary with standby and configure LS again.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, November 30, 2015 3:47 PM
    Moderator

All replies

  • Hi!

    The way you propose will most likely work although I would not go down that route... The safest way is:

    1) Disable all LSRESTORE and LSCOPY jobs. (That's enough to now cause you headaches.)

    2) Recover the databases

    3) Once you are done restore the last full backup you have (Take one that is fairly new...)

    4) Enable all jobs again

    this of course only works if your databases are small enough to allow full restore...

    Sunday, November 29, 2015 2:43 PM
  • 1) To remove it cleanly, Right Click on the database-> Properties-> Transaction Log Shipping and uncheck log shipping. Click ok. That drops the logshipping jobs cleanly as well.

    2) 2nd method is almost ok. Just that if your testing takes long time then T log backups of Primary will be bigger after testing. Also, can your primary remain without T Log backups during testing? Is that acceptable as during testing, there is no recovery on primary using t log backups if a disaster strikes.

    Monday, November 30, 2015 9:48 AM
  • 1. I suggest you are asking step to break logshipping. If so please read below link

    Remove Logshipping

    2. I would not suggest taking file system backup. Before breaking logshipping make sure that whatever log backup is generated is restored on Secondary(dont call it slave) if not manually copy and restore it so as to avoid as less data loss as possible. You can use below queries

    select * from msdb.dbo.log_shipping_monitor_primary--on primary
    go
    select * from msdb.dbo.log_shipping_monitor_secondary--on secondary

    to see what log backups you have and what is not restored.

    After your testing is done and since you only did read operation on Secondary. Take full backup of primary and restore on secondary with standby and configure LS again.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, November 30, 2015 3:47 PM
    Moderator