Skip to content

Log shipping Manual Failover

    The primary reason(s) you will want to Log shipping Manual Failover the primary database to the secondary database is when you need to:

    • Apply to the primary database with service packs
    • Correct a corrupt database
    • Replace hardware failure (disk is damaged)

    Steps:

    Find the last back up, copy and restore files

    On primary:

    Run the following script to find last backup made:

    On secondary:

    Run the following script to find last copy and restore made:

    Use msdb
    Go
    
    Select secondary_server, secondary_database, last_copied_file, last_restored_date ,last_restored_file,*
    From log_shipping_monitor_secondary
    

    From log_shipping_monitor_secondary

    • run the last tail tlog backup of primary
    • run the copy and restore jobs on secondary
    • disable all three jobs
    • run tlog back up on primary with no recovery
    BACKUP LOG [Test] 
    TO  DISK = N'C:\s\Tail.trn' 
    WITH 
    NORECOVERY 
    GO
    
    • copy paste the tail tlog backup from primary to secondary folder
    • restore secondary database with the tail backup with recovery mode
    RESTORE LOG [Test] 
    FROM  DISK = N'C:\d\Tail.trn' 
    WITH  NORECOVERY 
    GO
    
    • on secondary database, start the log shipping process, using the secondary share folder
    • set up initial primary as secondary using the primary share folder
    • notice no need to take a full back up as the initial primary has been initialized
    • notice that we have 3 new jobs recreated for the reverse log shipping process
    • delete the old jobs to avoid confusion
    • validate that the new failed over database is accessible
    --ON PRIMARY SERVER
    
    Use msdb
    Go
    
    Select primary_server, primary_database, last_backup_date, last_backup_file ,*
    from log_shipping_monitor_primary 
    
    --1. take a final tail log backup for any transactions not backed up if the transaction log numbers dont match
    
    --2 diable all three jobs
    
    --3. run the following tail log backup with no recovery on primary server
    
    BACKUP LOG [Test] 
    TO  DISK = N'C:\s\Tail.trn' 
    WITH 
    NORECOVERY --<< the norecovery mode will make the database in a restoring state
    GO
    
    --4. copy paste this last tail log from primary to secondary.  
    
    --go to secondary server and exectue step 5
    
    RESTORE LOG [Test] 
    FROM  DISK = N'C:\d\Tail.trn' 
    WITH  RECOVERY
    
    -- ON SERVER SECONDARY
    
    Use msdb
    Go
    
    Select secondary_server, secondary_database, last_copied_file, last_restored_date ,last_restored_file,*
    From log_shipping_monitor_secondary
    
    --5.
     Restore Log
    
    RESTORE LOG [Test] 
    FROM  DISK = N'C:\d\Tail.trn' 
    WITH  RECOVERY
    
    
    --6 start the log shipping process from start making this the primary, and the original primary the secondary
    

    Also See:

    SQL Server 2019

    Change Roles Between Primary and Secondary