Share via

SQL reverse log shipping techniques.

  • Article

Question

Monday, December 9, 2013 6:31 AM

I have been testing the Reverse Log Shipping technique as in http://sqlmag.com/database-high-availability/3-log-shipping-techniques. I was able to do reverse log shipping if I leave out the 2 optional steps7 and 8 below. If I include step 7 & 8, I found the new secondary database will always have a status of "restoring" rather than "standby & Readonly". i did this a few times over and over to make sure it is right. But I am not sure what went wrong and causing the new secondary DBs in restoring mode rather than Standby & RO mode even the LSbackup, LSCOPY & LSRESTORE run after step 9.

Can someone shed some light on this?

Thanks & regards,

Irene

 

Reversing Log Shipping

Reversing log shipping is an often overlooked practice. When DBAs need to fail over to a secondary log shipping server, they tend to worry about getting log shipping back up later. This is especially true in the case of very large databases. If you're using log shipping as your primary disaster recovery solution and you need to fail over to the secondary log shipping server, you should get log shipping running as quickly as possible. With no disaster recovery failover in place, you might be running exposed.

Reversing log shipping is simple. It doesn’t require reinitializing the database with a full backup if performed carefully. However, it’s crucial that you remember the following:

  • You need to preserve the log sequence number (LSN) chain.
  • You need to perform the final log backup using the NORECOVERY option. Backing up the log with this option puts the database in a state that allows log backups to be restored and ensures that the database’s LSN chain doesn’t deviate.
  • The primary log shipping server must still be accessible to use this technique.

To fail over to a secondary log shipping server, follow this 10-step process:

  1. Disable all backup jobs that might back up the database on both log shipping partners.
  1. Disable the log shipping jobs.
  1. Run each log shipping job in order (i.e., backup, copy, and restore).
  1. Drop log shipping.
  1. Manually back up the log of the primary database using the NORECOVERY option. Use the command
    BACKUP LOG [DatabaseName]
      TO DISK = 'BackupFilePathname'
      WITH NORECOVERY;
    where DatabaseName is the name of the database whose log you want to back up and BackupFilePathname is the backup file’s pathname (e.g., Z:\SQLServerBackups\TLog.bck).
  1. Restore the log backup on the secondary database using the RECOVERY option, and bring the secondary database online. The primary and secondary databases have now switched positions.
  1. Back up the log of the new primary database (optional).
  1. Restore the log on the new secondary database using the NORECOVERY option (optional).
  1. Reconfigure log shipping.
  1. Re-enable any backup jobs that were disabled.

Note that step 7 and step 8 are listed as optional because they’re not required for establishing log shipping. However, I recommend performing these steps to ensure that the log shipping configuration will proceed without any problems.

With a few minor adjustments, this 10-step process works with multiple secondary log shipping databases. You perform the same basic steps, keeping in mind that the other secondary databases will still be secondary databases after the failover. After you back up the log on the new primary database, you should use the NORECOVERY option to restore that backup on all the planned secondary databases. You can then add them as secondary databases to the new primary database.

All replies (6)

Monday, December 9, 2013 8:41 AM

Hi Irene,

As per the step 8 you are restoring the log backup using the NORECOVERY option which is bringing the database to restoring state. 

In Step 9 when you are reconfiguring logshipping, then select the Standby mode option on the restore transaction log tab as shown below.

Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

Monday, December 9, 2013 2:59 PM

It is similar to the same of configure of logshipping looks like you have used the taillogbackup with No recovery option instead that you have to specify the WITH STANDBY in your restore command

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Tuesday, December 10, 2013 5:32 AM

Hi Rama,

Tried your suggestion but it didn't work.
Step8 - I run  (Restore LOG [NEW_SEC] from disk='NEW_PRIM_taillog.bak' with STANDBY='undo.ldf'. )
        The new secondary DB is now showing status of "STANDBY/READONLY" in SSMS. I should have checked it in the DMV to confirm.
Step9 - reconfigure log shipping ok.
        LSBACKUp/LSCOPY/LSRESTORE all run successfully.

I added the following steps to test if the new secondary is in sync with the new primary.
step11 -  I created a few new tables and insert new rows to existing table on the NEW_PRIM.
       - rerun LSBackup/LSCopy/LSrestore.
Step 12 - query the database on NEW_SEC, I can't see any new update and new tables I created in step11.

WHY??
From the NEW_SEC instance, the ERRORLOG shows
"The database 'NEW_SEC" is marked RESTORING and is in a state that does not allow recovery to be run". The NEW_SEC database is
still in RESTORING status even after running Recovery with Standby option?? Even the SSMS is showing STANDBY/READONLY status. Even the LSRestore job completed.I should
check the DMV to confirm next time.

I don't think step8 can be run with STANDBY option as there is no prior "restore database with standby" before this.
Please refer to http://technet.microsoft.com/en-us/library/ms178034(v=sql.105).aspx for an example on how to use RESTORE with STANDBY option.

The 10 steps procedure is alright so far with my testing if you take out the optional steps 7 & 8.
But i would like to know what are the problems people may have experienced before and hence recommended steps 7 & 8.

Thanks & regards,
Irene

Tuesday, December 10, 2013 5:37 AM

I think it is better to drop the existing log shipping in step 4. This will definitely clean up the catalog table/view in the both the primary and secondary instances. Otherwise, you will see the msdb.dbo.log_shipping_primary_databases and msdb.dbo.log_shipping_primary_secondaries still contain values of the previous configuration. But in SQL 2008, after the switchover, if you run "restore database new_sec with recovery" to bring up the database, you can then clean it up (by uncheck the log_shipping property) afterward. In SQL 2005, you may not be able to do it and have to manually delete the rows.

There is nothing wrong with running step 4.

I am only having issue with the 2 optional steps 7 & 8.

Tuesday, December 10, 2013 5:38 AM

Hi Alankar,

This is exactly what I do, but the database NEW_SEC is still showing "restoring" state instead of "Standby/Readonly" status.

Thanks & regards,

Irene

Thursday, January 9, 2014 9:18 AM

Hi Aemo,

When you are switching roles in the log shipping at step 8 the why are you not taking a full backup of the new Primary DB and restoring it on the New Secondary DB with the Restore with Stand BY option.

I think if you use a full backup instead of log backup in step 8 the issue shall get resolved because when we set up log shipping, the first backup that is used to restore the DB either in No recovery or in Standby option is the full backup.

Try it out. Hope it should solve the issue.

Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005