Log Shipping Fail-over

I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible. My Steps for fail-over will be

  1. Copy and restore T-logs (WITH RECOVERY) that are not applied, to put the database online
  2. Since the Primary in not accessible so I cannot take a T-log and put it in restoring state
  3. Disable the above two jobs
  4. Change the application connection strings to the new primary.

My confusion arises what should I do when the Old Primary comes live again:

  1. Should I take the T-log back up and put the database in restoring state so that I can set it up as a secondary and then fail-over to make it primary.

If that’s the case then wont the log shipping fail if I want to configure without initializing (since I have the database in restoring state) as it will look for the last T-log which I Have not applied to my new secondary as the primary was not accessible during fail-over.

Thanks in advance


The issues and complications you mention are ones I've wrestled with myself. Quite a while back, I did my best to script out a solution to help make it easier to fail over and also to fail back. I would suggest going to codeproject.com and searching for "Streamline Log Shipping Failovers" to look at the quick article I wrote there. You can download the SQL script and see if it's helpful for your situation. Essentially, it is a SQL script that you run on the Secondary and it dynamically generates the scripts you need to fail over and later fail back.

I have used it many times in a Production environment to perform DR tests. I have also used it once in a true Production down situation and it really helps streamline the process.

| improve this answer | |

Consider there couple of minutes of down-time and for the Log Shipping fail-over. Also, the flexibility with Log Shipping is just to make sure the LSN in-sync in both sites which can be achieved by tracking all the Log backups

In you case you need perform the following steps:

  1. Perform Tail-Log Backup BACKUP ... WITH NORECOVERY, so that it will leave the database into restoring.. state, no new data would be inserted by any application
  2. Copy that backup into Original Primary and perform RESTORE ... WITH RECOVERY. To make LSN in-sync you may need restore other log backups prior to the Tail-log restore (if anything backups performed).
  3. Switch applications back to original primary - Database and applications are back to normal here

To establish Log Shipping: Manually perform a Backup at Original primary and restore with NORECOVERY at LS site to make LSN in-sync between sites. once the restore completed, you can enable the Log Shipping jobs and run them in following order:

  1. Backup Job at Original Primary
  2. Copy Job at LS Site
  3. Restore Job at LS Site

P.S: In case of RESTORE Job failure. You need to find LSN number that Restore job indicates in SQL Job History -> Step Details section, copy that LSN number to find the missing backup using following script (run it in LS Primary Replica). Script column of the result is the fix

Declare @LSN numeric(25,0) = 6767000003108800091;  -- add your LSN here

SELECT      [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            [b].[type] ,
            [b].[first_lsn] ,
            [b].[last_lsn] ,
            [b].[checkpoint_lsn] ,
            CASE    WHEN Type = 'L'
                        Then 'RESTORE LOG '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
                    WHEN Type = 'D'
                        Then 'RESTORE DATABASE '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
            END as Script
FROM    [msdb].[dbo].[backupset] AS [b]
            LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
WHERE b.first_lsn = @LSN or b.last_lsn = @LSN or b.checkpoint_lsn = @LSN or b.database_backup_lsn = @LSN

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.