Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

Steps for fail over log shipping database from primary to secondary during DR drill and switch back

Hi there,

  Could anyone share the steps involved during DR drill for failing over the log shipped databases and app team is gonna do some tests on the secondary database(which will become primary after faiover) then switch it back to the original stake after testing(ie., secondary will again goes to standy/read only) and primary will become active with the logs should resume its syn up job.

Please note, I am running MS SQL Server 2008 R2 enterprise edition on both the servers.

thanks
Deepak
0
Deepak Kumar
Asked:
Deepak Kumar
1 Solution
 
Emmanuel AdebayoGlobal Windows Infrastructure Engineer - ConsultantCommented:
Hi,

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.
    2. Disable the log shipping jobs.
    3. Run each log shipping job in order (i.e., backup, copy, and restore).
    4. Drop log shipping.
    5. 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).
    6. 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.
    7. Back up the log of the new primary database (optional).
    8. Restore the log on the new secondary database using the NORECOVERY option (optional).
    9. Reconfigure log shipping.
    10. Re-enable any backup jobs that were disabled.
For more info check the following links
http://dbasqlserver13.blogspot.co.uk/2013/12/failover-and-failback-in-logshipping.html
https://msdn.microsoft.com/en-us/library/ms191233.aspx

regards
0
 
Deepak KumarEngineerAuthor Commented:
Thanks Emmanuel for your help!! I really appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now